Sql-server – Shrink Transaction Log While Using AlwaysOn Availability Group

shrinksql serversql-server-2012transaction-log

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:

enter image description here

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?

Best Answer

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.