How to Shrink SQL Server Database Log in Availability Groups using TSQL

In my previous posts, I have already spoken about the best practices of Shrinking Log files and Database Files. From the recommendations, it is quite evident that Microsoft support actively discourages you to perform any Database or Log shrink operations. Instead, follow all the best practices that are already mentioned in my previous posts. However, sometimes in a nonproduction environment, you might want to do this as a one-time activity. In this post, I will explain how to shrink SQL server database log in Availability Groups using T-SQL.

Can you Shrink your Database Log files while being a part of an Availability Group?

Yes, you can. You can only run the commands on a Database which is acting as “Primary.”

// This Shrinks your Log Files to a 100 MB File //
DECLARE @dbname sysname
DECLARE @sqlstmt varchar(max)
SET @dbname = 'db1'
SET @sqlstmt = 'use [' + @dbname + '];DBCC SHRINKFILE(2, 100);'
IF sys.fn_hadr_is_primary_replica ( @dbname ) = 1 
BEGIN 
PRINT 'Shrinking file'
EXEC (@sqlstmt)
END

Also as stated previously, running this command could generate a lot of log activity if you are shrinking the complete database – which will cause a LOT of AlwaysOn endpoint traffic. It will also most likely cause secondary replica(s) to lag behind and severely hinder performance during this process. Depending on how big the database is and how much you are shrinking, you may want to take the database out of the AG, shrink it, and then re-configure the database for AG (noting that you will need to re-seed the secondary copies).

I would also recommend not use the “SHRINKDATABASE” command. Instead, only “SHRINKFILE” – so you can be more targeted and selective in what you are doing.

Disclaimer:

The code snippet provided are not tested in a production environment. Therefore not recommended to be used in Production before testing it out in test environments.