Why can't I shrink transaction log?

Question

I’m converting some historic databases to read-only and trying to clean them up. I’d like to shrink the transaction logs to 1MB. I realize it’s normally considered bad practice to shrink transaction logs, but I think this is probably the exception to the rule.

The databases are set to SIMPLE recovery on SQL Server 2012 Standard. So I would have expected that after issuing a CHECKPOINT statement that the contents of the transaction log could be shrunk, but that’s not working.

I have tried:

• Manually issuing CHECKPOINT commands.
• Detaching/attaching files.
• Backing up / restoring database.
• Switching from Simple, to Full, back to Simple recovery.
• Shaking my fist at it in a threatening manner.

After each of those attempts I tried running:

• DBCC SHRINKFILE (N'MYDATABASE_2010_log' , 0)
• DBCC SHRINKFILE (N'MYDATABASE_2010_log' , 0, TRUNCATEONLY)
• DBCC SHRINKFILE (N'MYDATABASE_2010_log' , 1)

I’ve seen this error message a couple times:

Cannot shrink log file 2 (MYDATABASE_2010_log) because total number of logical log files cannot be fewer than 2.

At one point I tried creating a dummy table and adding records to it in an attempt to get the transaction log to roll over and move to the end of the file, but that was just a wild guess.

Here are the results of DBCC SQLPERF(LOGSPACE)

Database Name   Log Size (MB)   Log Space Used (%)  Status
MyDatabase_2010 10044.13    16.71015    0

Here are the results of DBCC LOGINFO:

RecoveryUnitId  FileId  FileSize    StartOffset FSeqNo  Status  Parity  CreateLSN
0   2   5266014208  8192    15656   0   64  0
0   2   5266014208  5266022400  15673   2   128 0

Does anyone have any idea what I'm doing wrong?

Answer 1

If you are unable to truncate and shrink the log file, the first thing that you should do is to check if there is a real reason that avoids the log to be truncated. Execute this query:

SELECT  name ,
    log_reuse_wait ,
    log_reuse_wait_desc ,
FROM sys.databases AS D

You can filter by the database name

If the value for log_reuse_wait is 0, the database log can be truncated. If the value is other than 0 then there is something that avoids the truncation. See the description for the log reuse wait values in the docs for sys.databases. Or even better here: Factors That Can Delay Log Truncation. If the value is 1 you can wait for the checkpoint, or run it by hand: CHECKPOINT.

Once you have checked that there is no reason that avoids the log file truncation, you can do the usual sequence of backup (log, full of incremental) and DBCC SHRINKDATABASE or DBCC SHRINKFILE. And the file should shrink or not.

If at this point the file is not shrunk, don't worry, the reason is the physical structure of the log file, and it can be solved:

The log file works as a circular buffer, and can only be truncated by removing the end of the file. If the used part of the circular buffer is at the end of the file, then it cannot be truncated. You simply have to wait until the used part of the transaction log advances, and moves from the end of the file to the beginning of the file. Once this happens, you can run one of the shrink commands, and your file will shrink without a glitch. This is very well explained in this page: How to shrink the SQL Server log.

If you want to force the log file active part to move from the end to the start of the buffer:

• do some quite heavy operation on the DB inside a transaction and roll it back, to move the transaction log pointer further
• repeat the backup, to truncate the log
• shrink the file. If the active part of the log moved far enough, the file will shrink