How can I shrink a SQL Server LDF file that has grown much larger than the database?

Question

The LDF (transaction log) file for one of my Nama databases has grown to around 145 GB, even though the database backup is only 3 GB and the MDF data file is also about 3 GB.

How can I bring the LDF size back down without affecting the database?

Originally posted at https://answers.namasoft.com/question/92/ on 2018-06-28.

Ahmed Qasid · Accepted Answer

Try the following steps:

Run the Shrink command on the database from SQL Server Management Studio.
If the log file is still huge, you can empty it using the following T-SQL script. Replace DBNAME with the real database name in every place it appears:

USE DBNAME
DECLARE @SQLStatement VARCHAR(2000)
BACKUP LOG [DBNAME]
TO DISK = 'nul:' WITH STATS = 1
DBCC SHRINKFILE(DBNAME_log, 1)
BACKUP LOG [DBNAME]
TO DISK = 'nul:' WITH STATS = 1
DBCC SHRINKFILE(DBNAME_log, 1)
BACKUP LOG [DBNAME]
TO DISK = 'nul:' WITH STATS = 1
DBCC SHRINKFILE(DBNAME_log, 1)
BACKUP LOG [DBNAME]
TO DISK = 'nul:' WITH STATS = 1
DBCC SHRINKFILE(DBNAME_log, 1)

The script alternates a log backup (discarded to nul:) with DBCC SHRINKFILE a few times, which is usually enough to release the unused space inside the LDF and bring it back to a normal size.

Originally posted at https://answers.namasoft.com/question/92/ on 2018-06-28.

How can I shrink a SQL Server LDF file that has grown much larger than the database?

1 Answers