LDF Truncation within an AON-Environment (SQL Server Availability Groups)

By Lukas Hillesheim, 8 August, 2023
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":
Figure 1: Cluster with two Nodes
Figure 1: Cluster with two Nodes
Die Datenbank [CLUS01] ist Teil von AON:
Figure 2: Database [ClusDB01] as part of AON
Figure 2: Database [ClusDB01] as part of AON
The table [order] is still empty:
Figure 3: Table [order] is still empty
Figure 3: Table [order] is still empty
The LDF file has the initial size and is still empty:
Figure 4: The LDF is still empty
Figure 4: The LDF 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%:
Figure 5: Enlarged LDF
Figure 5: Enlarged LDF
Figure 6: LDF (nearly) 100% filled
Figure 6: LDF (nearly) 100% filled

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.
Figure 7: LDF cannot be shrinked
Figure 7: LDF cannot be shrinked
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:
Figure 8: AON requires FULL Recovery Mode
Figure 8: AON requires FULL Recovery Mode

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:
Figure 9: Backup to NUL Device
Figure 9: Backup to NUL Device
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.