Blog Article & Copyright by Lukas Hillesheim
Shrinking the log file is an occasional administrative task in SQL Server. In some cases the logfile can only be shrinked by temporarily applying the SIMPLE recovery mode. The measures to solve the problem can harm the backup chain and - depending on your point of view - can be seen as a bug or an annoying behavior. With AlwaysOn Availability Group an additional problem arises, because a database that is part of AON cannot be switched to the SIMPLE recovery mode without resetting the AON cluster configuration. The following article shows a solution.
1. Test-Environment
My test environment consists of a domain controller and two cluster nodes that provide the AON group named "CLUS01":
Die Datenbank [CLUS01] ist Teil von AON:
The table [order] is still empty:
The LDF file has the initial size and is still empty:
2. Enlargement of the LDF
On both NODE01 and NODE02, after inserting 500,000 lines, the LDF file on the hard disk has grown and is filled up to nearly 100%:
3. Shrinking the LDF does not work
In order to shrink the LDF, the steps from A. and B. have usually to be applied:
A. Regular steps to reduce the LDF - applicable in an AON and a non-AON environment:
- - 1. Backup the LDF
- - 2. Trigger checkpoint process using command CHECKPOINT
- - 3. Physical shrink of the LDF using command DBCC SHRINKFILE(...)
B. Workaround - if DBCC SHRINKFILE does not succeed. The steps are only applicable in a non-AON environment:
- - 1. Temporarily switch recovery mode to SIMPLE and back to FULL
- - 2. Full backup
- - 3. Optional: Backup Log
- - 4. Rerun DBCC SHRINKFILE
In my test environment, the steps from A. are executed. The last command ends with a warning. This can happen occasionally in both an AON- and non-AON environment and makes the steps from B. necessary.
As expected, an attempt to apply B. ends with an error message. The database [ClusDB01] cannot be switched to SIMPLE recovery mode due the presence of AON:
4. Backup to a NUL-Device
Instead of temporarily switching to SIMPLE mode thereby indirectly initiating LDF truncation, it is also possible to back up the LDF to a NUL device. After that, the shrink command will work. The effect is, that the LDF is physically shrinked on both nodes:
Please also note the following:
- - Depending on your point of view, the log file can be treated as damaged by both the recovery mode switch and the NUL device backup. As a result, the backup chain is broken. Full backups that were performed prior to the NUL device backup can no longer be used for a point-in-time recovery. This is true for those point-in-times that are located after the recovery mode switch
- - To avoid further data loss, please run a full backup
The recommendations mentioned in this article are neither documented by MS, nor in the Internet communities. Please run tests in your own prod environment.