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.

Shrink SQL Server Database Log in Availability Groups

Before shrinking any of the Data Files or Log files, do keep in mind the repercussions of doing so. SQL Server availability groups use these log files for replicating data across replicas. Here are some highlights:

1. Since “Write-Operations” only occur in the primary database, perform the shrink operation on the primary server.
2. The Shrink-Operation might not give you results as you would typically expect in a standalone SQL Server.
3. Instead of shrinking the Database, determine why the log files have grown to such an extent.
4. If you do not find out the cause of the log file growth, then it might reoccur in the future.

Now that the expectations are being said let us know how to Shrink SQL Server Database Log in Availability Groups.

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.

Conclusion:

Here is an important notification for you. We have not tested the code snippet in a production environment. Therefore, we do not recommend the script to be used in production environment. Ensure adequate testing before using it out in production environments.

For any feedback or suggestions, please use the comment section below.

Disclaimer: The Questions and Answers provided on https://gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.