Shrink Transaction Log While Using AlwaysOn Availability Group

Question

We are using AlwaysOn Availability Group feature of SQL Server 2012. Regular full database backups and transaction log backups are done every day on the secondary database.

I have read here doing the transaction log backup on either the primary replica or the secondary replica will mark both replicas' transaction logs as reusable. Anyway, the transaction log backup size is big and can be reduce using shrink file:

I have restore the database locally and perform the shrink operation. The log file size was reduces to 160 MB.

My question is on which database should I perform a shrink operation over the transaction log file (primary, secondary or both)?

---

I guess in the past for several years no back ups of the log file are made, so it become so huge. Executing DBCC SQLPERF (LOGSPACE) I can see that only 0.06% of the file are used - there is no point for me to keep such huge size of the log file. In [sys].[database_files] I check that its max_size is set to -1 with growth to 65536 so I guess when it need more space it will get. Anyway, I can shrink it to 5% for example in order to prevent future growth. I am trying to find some confirmation that I it is not bad idea to do so.

---

Actually, back ups (on the database and the log files) are performed only on the secondary databases, so it will be easier to perform the shrink file on them, but will the primary log file size be reduced as well?

Answer 1

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.

Answer 2

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

Answer 3

NOTRUNCATE is applicable only to data files. The log files are not affected.

For more info related to SHRINKFILE please check official doc https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver16