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.

SQL SERVER - Transaction Log Full - Transaction Log Larger than Data File - Notes from the Field #001 timradney 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 (https://blog.sqlauthority.com)

Notes from the Field, SQL Backup and Restore
Previous Post
Big Data – ClustrixDB – Extreme Scale SQL Database with Real-time Analytics, Releases Software Download – NewSQL
Next Post
MySQL – How to Find mysqld.exe with Command Prompt – Fix: ‘mysql’ is not recognized as an internal or external command, operable program or batch file

Related Posts

1 Comment. Leave new

  • 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