Solved

SQL 2008 R2 Transaction Log Management

Posted on 2013-05-13
4
370 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
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

820 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