Solved

SQL 2008 R2 Transaction Log Management

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 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