Shrink Transaction Log While Using AlwaysOn Availability Group

In AGs writes can only occur on the primary. Shrink operations are writes. Therefore you must do the shrink on the primary. Note that the shrink may not shrink as much as you expect, your test on the restored DB had probably leveraged simple recovery model. Read How to shrink the SQL Server log for more info.

Do not shrink to 160MB. Determine why did the log grow to 121Gb so it does not repeat (you have a suspicion, would be nice to confirm if possible). Size the log to a size appropriate for your operational needs. Log growth is a serious problem, it cannot use instant file initialization and all your database activity will freeze while the log grows and is being 0-initialized. Users and apps hate it when it occurs. If you understand the impact and your users are OK, you can shrink once to a small amount (160MB is probably too small though) and let it grow until it stabilizes.


You can try:

  1. Database on all servers in Availability Group should be in Synchronized state.
  2. Move used pages to start of the transaction log, before you shrink it.
  3. Sometimes available free space of log is 99%, but SQL Server can't release unused space. Try to reboot each server in Availability Group in turn.
  4. Sometimes you need to bakup and shrink transaction log 2 times before MS SQL Server released free space (Cannot shrink log file (DB_Log) because the logical log file located at the end of the file is in use.).

Try this script:

    --Set current database inside job step or script
    --Check for Execute on Primary Only
    if (SELECT role
        FROM sys.dm_hadr_availability_replica_states AS a
        JOIN sys.availability_replicas AS b
            ON b.replica_id = a.replica_id
    WHERE b.replica_server_name = @@SERVERNAME) = 1
    BEGIN
        -- Use [test_db] -- Not working for MS SQL 2014, just comment this line and set current database inside job step or script
        -- 1) Bakup Trn
        BACKUP LOG [test_db] TO  DISK = N'D:\MSSQL\Backup\test_db.trn' WITH NOFORMAT, INIT,  NAME = N' Trn Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
        -- 2) Move used pages
        DBCC SHRINKFILE (N'test_db_log' , 3000, NOTRUNCATE)
        -- 3) SHRINKFILE Log
        DBCC SHRINKFILE (N'test_db_log' , 3000)
    END