How to correctly Shrink Log File for SQL Server

In case your transaction log reaches abnormal dimension (e.g. you forgot the backup of the log in your maintenace plan...) it is highly useful this script that allows you to safely shrink the transaction log to the wanted dimension
Copy Embed Code
<iframe id="embedFrame" style="width:600px; height:300px;"
Click on the embed code to copy it into your clipboard Width Height
Leave empty to retrieve all the content Start End
USE AdventureWorks GO -- Use some dynamic SQL just only not to re-write several times the name -- of your db, or to insert this snippet into a loop for all your databases... DECLARE @dbname varchar(50) = 'AdventureWorks'; DECLARE @logFileName varchar(50) = @dbname + '_log'; DECLARE @SQL nvarchar(max); SET @SQL = REPLACE('ALTER DATABASE {dbname} SET RECOVERY FULL;', '{dbname}', @dbname); EXECUTE(@SQL); DECLARE @path nvarchar(255) = N'F:\BCK_DB\logBCK' + CONVERT(CHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','') + '.trn'; BACKUP LOG @dbname TO DISK = @path WITH INIT, COMPRESSION; DBCC SHRINKFILE(@logFileName); -- determine here the new file size and growth rate: SET @SQL = REPLACE('ALTER DATABASE {dbname} MODIFY FILE (NAME = ' + @logFileName + ', SIZE = 32000MB, FILEGROWTH = 10%);', '{dbname}', @dbname); EXECUTE(@SQL); GO
If you want to be updated about similar snippets, Sign in and follow our Channels

blog comments powered by Disqus