Enterprise TechSQLHow to Shrink SQL Server Database Log in Availability Groups using TSQL July 30, 201612112 views0Share By IG ShareIn 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) ENDAlso 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 TechList of the Best Threadripper Pro Workstations – W-3100 Xeon W-3200 & W-1200 Alternatives By IGMay 25, 2021
Enterprise TechList of the Best HPTX Cases – Huge PC Cases for Large BuildsThe computer cases have been one of the essential aspects when it comes to enjoying ...
AzureBest Industrial IoT Routers & Gateways For AWS and Azure IoT ServicesIn this article, we are going to list out the Best Industrial IoT Routers for professional ...
Enterprise TechWhat Is A DataCenter Power Distribution Unit (pdu) Server Rack CabinetsNo matter whether you call it a server closet or server room or a cabinet, ...
Storage SystemsWD SE vs WD Red Pro Specifications Comparison – Enterprise Class NASThe HDDs or the Hard Disk drives are slowly moving into oblivion with the advancements ...