Shrink Microsoft SQL Database Transaction Logs

posted 7 Jan 2014, 06:01 by Tristan Self
PROBLEM:
 
Your SQL transaction log has grown to be really big, this might be because a load of data was added, or your backup procedures were failing to truncate the logs.

SOLUTION:
 
Take a backup of the database, then open the SSMS and then run this command:
 
USE DATABASENAME
GO
ALTER DATABASE DATABASENAME SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(DATABASENAME_log, 1024)
ALTER DATABASE DATABASENAME SET RECOVERY FULL WITH NO_WAIT
GO
 
Now take a full backup of the database to ensure you have a recoverable version.
 
Comments