SQL Server Shrink the Transaction Log
David | December 11, 2014Recently, I was asked to write a stored procedure to shrink the SQL Server transaction log.
Below is the very simple code to do this:
USE MyDatabaseServerInstance GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE Shrink_Log AS BEGIN SET NOCOUNT ON ALTER DATABASE MyDatabase SET RECOVERY SIMPLE -- Shrink the truncated log file to 1000 MB. DBCC SHRINKFILE (MyDatabase_log, 1000) ALTER DATABASE MyDatabase SET RECOVERY FULL END GO
I do not recommend doing this regularly because it breaks the transaction log chain and you won’t be able to reconstruct your database. Be careful. Until you do a backup, the transaction log will be in Pseudo-FULL mode. Only after doing another backup will your transaction log be back in FULL RECOVERY mode.
For more information you can go here
Enjoy!