My Daily Cloud Blog

Here is a good Script I use to Shrink does SQL Databases Translogs:

——————————————————————————
— Update the line below with the name of the database who’s transaction
— log you want to shrink.
——————————————————————————
USE name of the database
——————————————————————————
— Don’t change anything below this line.
——————————————————————————
GO
— Declare variables
DECLARE @SqlStatement as nvarchar(max)
DECLARE @LogFileLogicalName as sysname
— Alter the database to simple recovery
SET @SqlStatement = ‘ALTER DATABASE ‘ + DB_NAME() + ‘ SET RECOVERY SIMPLE’
EXEC ( @SqlStatement )
— Make sure it has been altered
SELECT [name], [recovery_model_desc] FROM sys.databases WHERE [name] = DB_NAME()
— Set the log file name variable
SELECT @LogFileLogicalName = [Name] FROM sys.database_files WHERE type = 1
— Shrink the logfile
DBCC Shrinkfile(@LogFileLogicalName, 1)
— Alter the database back to FULL
SET @SqlStatement = ‘ALTER DATABASE ‘ + DB_NAME() + ‘ SET RECOVERY FULL’
EXEC ( @SqlStatement )
— Make sure it has been changed back to full
SET @SqlStatement = ‘SELECT [name], [recovery_model_desc] FROM ‘ + DB_NAME() + ‘.sys.databases WHERE [name] = ”’ + DB_NAME() + ””
EXEC ( @SqlStatement )
——————————————————————————

Were is red, enter the name of the Database that you would like to shink the logs.

By http://ottoradke.com