A very common problem that I see time and time again is the Log file growth of Microsoft SQL Server .LDF files.

This problem can cause service outages when a hard disk is filled up completely by these massive LDF files.

The problem happens when a SQL Server Database is configured for Full Recovery mode (often the Default). In Full Recovery mode, the SQL Log files (.LDF files) must be backed up themselves, in addition to backing up the SQL Database. Many people get confused and think they only have to backup the SQL Database file.

Solving the problem

Ideally, you should start backing up the SQL Log files. They are there for a reason, and full recovery mode is awesome because it allows you to restore a database to a point in time, specifically, to the point in time that you backed up the SQL database + and then the last SQL transaction log backup. So if you perform a SQL Full backup at 8pm nightly, and a SQL transaction log backup the next day at 12:00 Noon, then you can restore to any point in time up until 12:00 Noon.

If your recovery point objective (RPO) allows you to lose up to a day’s worth of data, and you are okay with restoring only to the previous night’s full backup, then by all means, change your database recovery mode to Simple and avoid the hassle of backing up the SQL Transaction logs altogether!

You might say, wait, this is all well and good, but I have a problem right now that I am trying to solve. My .LDF files have filled up my hard disk, and I need to clear them out now! First, before you proceed, it is important to understand why the logs are growing, otherwise you may find yourself having to continuously repeat this procedure. Log growth is normal when there are lots of write transactions into the database. The solution is to backup the transaction logs more frequently so that they are logically truncated, and that can prevent the physical file from growing too large.

First, find out if your database is in an Availability Group or a Database Mirror. Because your options are limited in this case. If your database is not in an AG or DM, then just switch the recovery model to simple, shrink the Log file using SQL Management Studio, then if needed, switch the recovery model back to full.  This method is the quickest, but  you lose the ability to restore to a point in time from the last full backup, so perform this at your own risk. In fact, all advice on this blog is for educational purposes, and I provide no warranty, and I assume no responsibility if you follow any of my advice. =)  If you have available disk space,  you can always backup the SQL transaction log first before performing switching the recovery model from full to simple.

Okay, so assuming you need to shrink a log file that is in an AG or DM, then the only method I have found that works is to perform the following (again, use at your own risk):

1. Identify the culprit log files by running this query in SQL Management Studio:
DBCC SQLPERF(LOGSPACE);

In my case, this showed two databases with log files > 65GB.

2. Next, backup the Log file to free up space within the file (logically/virtually). Ideally, if you had enough disk space, you would backup the log file to an actual file somewhere. Otherwise, if you are okay with an RPO of 24 hours (to your last full backup) then you can backup to a null device (great blog article here describing this method, please heed the disclaimers).

BACKUP LOG myDatabaseName TO DISK=’NUL:’

Note: Technically you should be able to run this command against the primary replica or the secondary replica, and the log file will be truncated in both places according to this blog article.

3. Next, verify if the log file is in a state that will allow shrinking. If your status is ‘2’ then you will need to proceed to step 4, otherwise if the Status is ‘0’ (Zero) then you can skip to step 5.

Use myDatabaseName
GO
dbcc loginfo

4. This step will reset the log file so that you can physically shrink it in step 5. Again, this step assumes that you are okay with a 24 hour RPO as you will only be able to restore to your last full backup.  I’ve worked with enough DBA’s that if I don’t add these disclaimers at each step then they will certainly spam the comments with ‘don’t ever do this step’ =)

DBCC SHRINKFILE (myDatabaseName_Log, EMPTYFILE);

Next, re-run step 3 (dbcc loginfo) and verify that Status is now 0 instead of 2. If it is, then proceed to step 5, otherwise re-run step 2 and 4.

5. Now that the transaction log has been backed up, and emptied, it is now possible to physically shrink the size of the log file on disk with this command:

DBCC SHRINKFILE (myDatabaseName_Log, 500);   –This would physically shrink the database size to 500 Megabytes.

Important: you can only shrink files against the primary replica. The good news is that once you shrink the primary, the physical size of the secondary replicas will shrink too, so you only need to do this in one place.

Hint: dbcc opentran shows if there are open transactions that could block the shrink operation.

Hint #2: If the log files still will not shrink, check to make sure that the secondary replica database is not marked as Suspect. In that case, you will need to manually remove the suspect database from the secondary first before the shrink operation will work.

Note: Before determining the size of 500Mb to shrink to, you may want to consider how much of the log file is in use, otherwise the shrink operation will not work. Also, you may want to consider allowing the size of the log file to be 25% of the size of the physical database file (.MDF) because otherwise when log growth happens, the database operations will block all active transactions and that will cause latency within applications (imagine users complaining).

You can determine how much of the log file is in use by running this query:

Use myDatabaseName
GO

SELECT name ,size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

So to determine the size of the log file to shrink to, subtract the “AvailableSpaceInMB” from the physical database size reported by the command: DBCC SQLPERF(LOGSPACE);. Then add some cushion so that future physical log growth does not block transactions from occurring.