Enterprise TechSQL How to Shrink SQL Server Database Log in Availability Groups using TSQL July 30, 20161235 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. 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: Since “Write-Operations” only occur in the primary database, perform the shrink operation on the primary server. The Shrink-Operation might not give you results as you would typically expect in a standalone SQL Server. Instead of shrinking the Database, determine why the log files have grown to such an extent. 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://www.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. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0 IG Website Twitter
TSQL How to Effectively split a string using STRING_SPLIT function in SQL server By DataGeekApril 15, 2019
Enterprise Tech How to use Azure Ad Join on Windows Server 2019? & Gateway Access Just like Microsoft Windows 10, the Microsoft Server 2019 also lets you make use of ...
Enterprise Tech What is VMware VMotion? Limitations & Requirements – Storage & Shared If you are in the IT industry, especially infrastructure professionals, you may have heard the ...
Cloud Computing Differences Between VMWare SRM Standard and Enterprise Licensing Disaster Management is one of the essential aspects of any virtualization technique. VMWare, being one ...
Enterprise Tech Cisco WebEx vs Skype for Business: Differences and Comparisons The workforce today has become completely mobile. With that change in the work strategy, the ...