CODEWITHSUNDEEP
asp.netsql-serverdatabase-backupsapplication-poolldf
S.A.
S.A.

Reputation: 21

Backup Log database SQL Server

I have a database on SQL Server Express used for 3 Asp.net applications. The Recovery Model is Full.

Every day I execute a backup with this code

BACKUP DATABASE @databaseName TO DISK = @file WITH INIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
BACKUP LOG @databaseName TO DISK = @fileLog WITH NOFORMAT, NOINIT,  NAME = N'Database-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

When the Start Mode of Application Pools are on OnDemand the LDF is reduced every day; when I use AlwaysRunning the log is not reduced.

I want use application pools with always running. Can I use shrink to reduce log size? Even if users are using websites?

ALTER DATABASE [DatiMachinaWeb] SET RECOVERY SIMPLE
DBCC SHRINKFILE(DatiMachinaWeb_log)
ALTER DATABASE [DatiMachinaWeb] SET RECOVERY FULL

Am I wrong? How can i solve? Thanks

Upvotes: 0

Views: 187

Answers (1)

pifor
pifor

Reputation: 7882

Yes you can use DBCC SHRINKFILE to reduce log size online.

But switching temporarily to simple recovery mode is going to break log backup chain that you would need if you need to do recovery using a backup created before the log shrink.

In general it should not be needed to shrink the log: normally you should either backup log more often or just have the right size adapted to the generated logging activity and to the frequency of log backups.

Upvotes: 1

Related Questions