The issue

You are trying to shrink your database but the system will not release the space to allow you to shrink the log files, even after performing a backup of the data.

Here are some trouble shooting tips.

Who is holding the log open?

One reason why you cannot shrink the log file is that the log file may be being held for some reason.

Two main reasons are:

1. Log Backup
2. Replication

The way you can check if there is any locking on the log file is with the following statement.

SELECT name, log_reuse_wait_desc FROM sys.databases; 

This will return the log_reuse_wait_desc which contains the values as follows:

• NOTHING
  • This should mean that the log file is ready to be cleared or shrunk if needed.
• LOG_BACKUP
  • Performing a LOG backup should clear the LOG lock and allow you to again shrink the log file.
• REPLICATION
  • This one is tricky, see my example using AWS DMS (Data Migration Services)

Log file size

The other item to check in SQL is the size an usage of your log file.

I think of a log file like a virtual machine dynamically expanding hard drive.

As you use the database the log space increases as required and gets bigger and bigger physically on disk, but as the log file is used and the files are released internally the database may only be using a small amount of that drive but the size on disk never gets released and it stays at the maximum size it previously got too.

In my current example my log file is 13GB in size, and using 78%.

To see this run the following SQL Script

dbcc sqlperf(logspace);

Note: You often want to view the logspace according to Size
The following script allow you to load the logspace results to a temp table and sort according to your preferences.

Drop Table If Exists #logspace

Create table #logspace (
	DatabaseName varchar(254),
	LogSizeMB numeric(18,6),
	LogSpaceUsedPercent numeric(18,6),
	LogStatus int,
	LogSizeFreeMB as LogSizeMB * ( (100 - LogSpaceUsedPercent) / 100),
	LogSizeUsedMB as LogSizeMB * (LogSpaceUsedPercent / 100)
)
Insert Into #logspace
exec ('dbcc sqlperf(logspace)');

select * 
from #logspace
Order by LogSizeFreeMB desc

AWS DMS Replication holding the log locked example

If you are running any kind of SQL Replication the Sync process may be holding onto the transaction log and I have experienced using AWS DMS the replication is failing and so the replication is holding a checkpoint in the past that is stopping the log file from being released and moving forward, (I actually have this occurring as I write this).

To clear this log in the example of the AWS DMS process can be quite simple.

In this scenario the log file is sitting at 13 GB and internally using 75% of that.

To clear this in this instance I did the following:

1. Run the above SQL and check how it is held, and it was returning REPLICATION
2. Stopped the Data Migration Task, this effectively stops the replication.
3. Logged onto the source box where the Local Publication is created by AWS DMS and right-click deleted the local publication
   • The publication itself is created by AWS DMS so it will be re-created as required when the task is started.
4. Run the LOG Locking Check and it says NOTHING.
5. Restart the Data Migration Task.

The process is now running and the logfile still says Nothing.

You can now run a SHRINK on the file.

DBCC SHRINKFILE (N'yourDbName_log' , 0, TRUNCATEONLY)
GO

Log Backup and Shrink

So you have identified the log backup is required but you have full backups from the night before and are not really interested in the transaction log backup.

You can perform a log backup and discard the backup.

Check the database properties and check the Logical Name of your log file under the “Files” page, you will need this later, mine was myDb_log

On my example the following is the condition:

• LogSize – 98 GB
• Log Used % – 99.9
• Log Locked Status – LOG_BACKUP

1. Backup the log file – discarding it;

BACKUP LOG myDB TO DISK='NUL:' 

This will backup the log file and clear the LOG_BACKUP hold.

After this is complete check the log_reuse_wait and it should now be NOTHING

Check the file size of the log file, the % use should be <1%, mine was 0.065% used

Keep running this Backup until the log_reuse_wait_desc is NOTHING, if you wait too long to shrink on an active database is can move back to the LOG_BACKUP state in which case the Shrink will not work.

2. Shrink the log file

If you are needing to recover the disk space you can run a shrink on the log file with TRUNCATEONLY.

TRUNCATEONLY. Releases all free space at the file’s end to the operating system but doesn’t perform any page movement inside the file.

Given there is no files inside the file this should be quick.

Run the following:

DBCC SHRINKFILE (N'myDb_log' , 0, TRUNCATEONLY)

My log file is now 41MB and I have just reclaimed 98GB of disk space :

Note: On a number of occasions I had to run the log backup and shrink twice to get it to actually shrink the file. On the first attempt it did not shrink, but on the second attempt it did shrink the file