SQL 2008 R2 Transaction Log Management
Posted on 2013-05-13
We have a number of SQL 2008 R2 servers in which I have to manually shrink the log file periodically. Every now and then the log file gets up to 50GB.
When this happens, we manually do this:
ALTER DATABASE [DBFilename] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(DBFilename_Log, 100)
ALTER DATABASE [DBFilename] SET RECOVERY FULL WITH NO_WAIT
We were led to believe that when a backup of the database is done, it also truncates and shrinks the log file. This does not appear to be the case.
So, with all that said, can you please offer some advice on how we should be maintaining the database nightly or periodically so that the transaction log does not grow to ridiculous sizes?