I had a recent experience with a SQL Server 2012 database that was in SIMPLE RECOVERY MODE that was not releasing log space and the LDF file was growing and consuming excessive drive space. Generally a log backup is all that is required to reclaim log file space. In the case of this particular database, since it was in Simple Recovery Mode, it should have resolved itself automatically after each transaction completed. But doing a Log Backup or even a simple SHRINKFILE on the database wasn’t releasing the drive space.
DBCC SHRINKFILE (N’YourDatabaseLogFileName’ , 0, TRUNCATEONLY);
One thing I noticed was that the Log_Reuse_Wait_Desc showed that the log was in use with a “LOG_BACKUP” in process.
SELECT name as DatabaseName, log_reuse_wait_desc FROM sys.databases WHERE name = ‘myDBName’
Executing SP_WHO2 showed that there was one process running on the database that was doing a “GHOST_CLEANUP” and that process was blocking other process ids.
After waiting a day and monitoring the situation, it became clear that it wasn’t going to resolve itself anytime soon and the drive space was becoming more and more of an issue.
The issue was that the database in question, though primarily for reporting had a lot of insert and deletions with big data going in and out of it regularly. The GHOST_CLEANUP process was responsible for reclaiming the deleted record space. This process can fall behind in databases with a lot of transactions since it’s a single threaded process.
One option to resolve the GHOST_CLEANUP issue is to rebuild the indexes in the database. I issued a command (see SQL Authority: Rebuild-Every-Index) to rebuild all of the indexes in the database. After the indexes were rebuilt, SP_WHO2 showed that the GHOST_CLEANUP process completed.
But that still left the issue of the LOG_BACKUP not completing and still preventing the SHRINKFILE from effectively reclaiming the disk space.
Since the database as in Simple Recovery Mode anyway, the log file wasn’t valuable. Rather than waiting to see if the Log Backup would eventually catch up, I opted to resolve it quickly by rebuilding the log file.
Please note, that rebuilding the log (and even shrinking the log file) may result in not being able to use your latest FULL BACKUP to restore the database to a more recent point in time. It is recommended that if you implement this logic you also follow it up with another FULL BACKUP.
To rebuild the log file, you first need to put the database in Emergency mode. In SQL Server 2005+ it’s done with the ALTER DATABASE command. You’ll also need to make sure you’re the only user in the database with only one process id active.
ALTER DATABASE MyDatabaseName SET EMERGENCY
Once in Emergency mode, you’ll need to open File Explorer and find the database’s .LDF log file and rename it or delete it. Then issue the following T-SQL to rebuild the log file:
Once the log file is rebuilt, you can take the database out of Emergency mode by setting it back ONLINE:
Afterwards, you can verify that the log file has been replaced with a new nearly empty .LDF file.
After your backup completes, verify that the LOG_REUSE_WAIT_DESC is back to normal. You will likely have to issue a CHECKPOINT to update the log reuse wait description after the backup completes.
SELECT name as DatabaseName, log_reuse_wait_desc FROM sys.databases WHERE name = ‘MyDatabaseName’
If the Log_Reuse_Wait_Desc shows “NOTHING” you were successful.