T-SQL: Generate SQL Script to Shrink Transaction Logs of All Databases

Here’s a quick and dirty way of generating the T-SQL script to shrink the transaction log of each database on your SQL Server instance:

 
EXEC sp_msForEachDB 'PRINT ''
USE ?
GO
ALTER DATABASE ? SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE(''''?_log'''',1)
GO
ALTER DATABASE ? SET RECOVERY FULL
GO''
'

I prefer generating the SQL rather than executing it directly so you can trim out the databases you don’t wish to shrink. It also gives you the option of then editing the file path of the log file from the default. I am sure there is way to query this information from the system as well, so if you know how, post a comment! 🙂 Hope this helps.

This entry was posted in T-SQL. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *