SQL SERVER – Transaction Log Full – Transaction Log Larger than Data File – Notes from the Field #001

I am very excited to announce a new series on this blog – Notes from the Fields. I have been blogging for almost 7 years on this blog and it has been a wonderful experience. Though, I have extensive experience with SQL and Databases, it is always a good idea that we consult experts for their advice and opinion. Following the same thought process, I have started this new series of Notes from the Fields. In this series we will have notes from various experts in the database world.

My friends at Linchpin People have graciously decided to support me in my new initiation.  Linchpin People are database coaches and wellness experts for a data driven world. In this very first episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains a very common issue DBA and Developer faces in their career, when database logs fills up your hard-drive or your database log is larger than your data file. Read the experience of Tim in his own words.


As a consultant, I encounter a number of common issues with clients.  One of the more common things I encounter is finding a user database in the FULL recovery model that does not make a regular transaction log backups or ever had a transaction log backup. When I find this, usually the transaction log is several times larger than the data file.

Finding this issue is very significant to me in that it allows to me to discuss service level agreements with the client. I get to ask questions such as, are nightly full backups sufficient or do they need point in time recovery.  This conversation has now signed with the customer and gets them to thinking about their disaster recovery and high availability solutions.

This issue is also very prominent on SQL Server forums and usually has the title of “Help, my transaction log has filled up my disk” or “Help, my transaction log is many times the size of my database”.

In cases where the client only needs the previous full nights backup, I am able to change the recovery model to SIMPLE and shrink the transaction log using DBCC SHRINKFILE (2,1) or by specifying the transaction log file name by using DBCC SHRINKFILE (file_name, target_size).

When the client needs point in time recovery then in most cases I will still end up switching the client to the SIMPLE recovery model to truncate the transaction log followed by a full backup. I will then schedule a SQL Agent job to make the regular transaction log backups with an interval determined by the client to meet their service level agreements.

It should also be noted that typically when I find an overgrown transaction log the virtual log file count is also out of control. I clean up will always take that into account as well.  That is a subject for a future blog post.

If your SQL Server is facing any issue we can Fix Your SQL Server.


Additional reading:

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

4 thoughts on “SQL SERVER – Transaction Log Full – Transaction Log Larger than Data File – Notes from the Field #001

  1. Pingback: SQL SERVER – Roundup Post – Notes from the Field – Year 2013 – Performance Tuning and Database Health | Journey to SQL Authority with Pinal Dave

  2. Pingback: SQL SERVER – Contest Post – Notes from the Field – Learning Performance Tuning and Database Health | Journey to SQL Authority with Pinal Dave

  3. Pingback: SQL SERVER – Round Up From Notes from the Field of Blog Posts of Tim Radney | Journey to SQL Authority with Pinal Dave

  4. I have a log file which is greater than the database size in gb. The recovery mode is set to full. At this point of time when I have come in there is no need to keep the recovery mode to full as we will never be going back to any old state of the database. I want to just get rid of these huge log files and shrink back to normal.
    So can I set recovery to simple. Take a full backup. then change back the recovery model to full. Is that allowed and possible and what are the advantage and disadvantage of doing that. I tried removing the log file from a test database by restoring the db file only and it worked on a machine. But I want to know from an expert like you

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