Shrinking Transaction log


Some days back, I had a discussion with one of my colleague about Shrinking a database log file. He told me that they had a database with a log size 10 times larger than the data file size, and they shrink it almost everyday!!! The database was 11GB in size, with 1 GB data file and 10 GB of log file size. The database was in Full Recovery model, and there was no log backup scheduled for the database. Every night, there was a schedule for full database backup.

I asked him to check, whether they can consider to change the recovery model of the database. Since they were not doing a log backup and the database was in Full recovery model, the transaction log file had grown because it contains all the inactive transactions. The only way to reuse the same space, is by taking a transaction log backup. However, if we change the recovery model to Simple, SQL Server will automatically truncate the inactive portion of the log file, which will help in reducing the transaction log file size. Point to remember is that, if the database is in Simple Recovery Model and there is an active transaction going on, then the transaction log file will grow large. To simplify the transaction log management, do one of the following:

  • If the database is in Full Recovery Model, schedule the transaction log backup along with the full backup. Make sure, you perform the transaction log backup multiple times to avoid the transaction log growth.
  • If you don’t want to take a transaction log backup, then make sure the database is in Simple Recovery Model. SQL Server will automatically manage the transaction log file.

I am sure, most of us, have seen this incident in the past. Hope this post may help someone.. someday..

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Backup - Restore, SQL Server, T-Log and tagged , . Bookmark the permalink.

3 Responses to Shrinking Transaction log

  1. manasdash says:

    This is cool. Recently we have done this activity in one of our SQL db and then after an achieve we purged the data(annual task). Good article Sudeepta.

  2. Jason Clark says:

    Nice post, thanks for sharing i have also found another helpful post for shrinking transaction log file see here: http://www.sqlserverlogexplorer.com/shrink-transaction-file/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s