Solved

SQL 2008 R2 Transaction Log Management

Posted on 2013-05-13
4
372 Views
Last Modified: 2013-05-27
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
0
Comment
Question by:TheMetalicOne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 2

Assisted Solution

by:Psihawk99
Psihawk99 earned 250 total points
ID: 39161789
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39161854
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
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 250 total points
ID: 39171079
"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
 
LVL 5

Author Closing Comment

by:TheMetalicOne
ID: 39199618
The issue was not backing up the TL, thanks
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question