Enterprise TechSQL How to Shrink SQL Server Database Log in Availability Groups using TSQL July 30, 201612969 views0 Share By IG Share 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. Note: If you buy something from our links, we might earn a commission. See our disclosure statement. 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. Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 1 Silly 0
Enterprise Tech List of the Best Threadripper Pro Workstations – W-3100 Xeon W-3200 & W-1200 Alternatives By IGMay 25, 2021
Enterprise Tech List of the Best HPTX Cases – Huge PC Cases for Large Builds The computer cases have been one of the essential aspects when it comes to enjoying ...
Azure Best Industrial IoT Routers & Gateways For AWS and Azure IoT Services In this article, we are going to list out the Best Industrial IoT Routers for professional ...
Enterprise Tech What Is A DataCenter Power Distribution Unit (pdu) Server Rack Cabinets No matter whether you call it a server closet or server room or a cabinet, ...
Storage Systems WD SE vs WD Red Pro Specifications Comparison – Enterprise Class NAS The HDDs or the Hard Disk drives are slowly moving into oblivion with the advancements ...