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
LVL 5
TheMetalicOneAsked:
Who is Participating?
 
Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
"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. "

---------------------------------------------------------------------------
free up the space used by committed transactions.
0
 
Psihawk99Connect With a Mentor Commented:
It does truncate the file but does not shrink it.  So if you have a 50gb log file, it may be only 5% full after a backup.

What's your database recovery method set to?  Do you perform regular transaction log backups?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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.
0
 
TheMetalicOneAuthor Commented:
The issue was not backing up the TL, thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.