Transaction Log Fills Up the Drive….Oh No!

Maybe your users cannot access your database, or maybe your preferred monitoring software is reporting limited or non-existent disk space on your log drive for a particular SQL Server.  Maybe you get a call from a Subject Matter Expert telling you their application is wigging out, or maybe you get a call from a user or even better, from your helpdesk.  We have all gotten that phone call or e-mail at one point or another.  A transaction log has gone awry and wreaked havoc on your limited drive allocated to the log files.  What next?

Do we know the database?  We could look at the drive and find the culprit.  However, we will need the logical name of the log file in order to shrink it, remember it isn’t always the same as the filename.  Run the following in SQL Server Management Studio (SSMS) in order to get the log file name:

SELECT name FROM sys.database_files WHERE type_desc = ‘LOG’

At this point, we can try to shrink the database by issuing the following command:

DBCC SHRINKFILE(‘<DB log file name>’, 0) 

If this does not shrink the file, then run the following command to see the log_reuse_wait_desc column (way over to the right):

SELECT * FROM sys.databases WHERE name = ‘<DB Name>’

The following values of the log_reuse_wait_desc are the most common reasons that the shrink operation has failed:

  1. CHECKPOINT:  This means that there is no database checkpoint in the transaction logs.  You should manually run the CHECKPOINT command or you could also perform a database backup.
  2. ACTIVE_TRANSACTION:  This indicates that there is an open transaction.  You can use DBCC OPENTRAN to find out what is going on (I will write another blog on this soon and link back here).
  3. LOG_BACKUP:  This indicates that the log file has wrapped around to the beginning of the file and the free space is somewhere in the middle of the file.  Run another log backup in order to move the free space to the beginning or end of the log file.

After completing one of the three afore-mentioned steps, we can now issue the SHRINKFILE command again….And all will be well with the universe.  Enjoy!

About these ads

About Ed Watson

Florida Gator and Certified SQL Server Enthusiast, Star Wars fanatic and is there anything else...oh yeah PS3! I am a geek and SQL Server DBA who dabbles with VMWare, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on January 10, 2012, in Lessons Learned, Maintenance and tagged , , , , . Bookmark the permalink. 4 Comments.

  1. Lilliana Bergen

    Great blog post. Will read on…

  2. Looking forward to reading more. Great article.Much thanks again. Really Cool.

  3. Remington Acosta

    Very informative article.Thanks Again. Really Cool.

  4. Wow, great article post.Really thank you! Will read on…

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

Follow

Get every new post delivered to your Inbox.

Join 1,291 other followers

%d bloggers like this: