Link to home
Create AccountLog in
Avatar of TheMetalicOne
TheMetalicOneFlag for Canada

asked on

SQL 2008 R2 Transaction Log Management

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?

Thank you

Paul
SOLUTION
Avatar of Psihawk99
Psihawk99
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Aneesh
seems like you are not taking the transaction log backups, once the TL backup is done it will free up the space used by committed transactions.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of TheMetalicOne

ASKER

The issue was not backing up the TL, thanks