Tuesday, August 6, 2013

Write a T-SQL script to shrink the Transaction Log file of SQL Server Database



For shrinking the transaction log of any SQL Server database, simply run the below script on that database:



USE YourDatabase_Name
GO

/*Declaring Variables*/
DECLARE @Database_Name NVARCHAR(100)
DECLARE @SQLString NVARCHAR(1000)

/*Setting the Database Name to variable*/
SELECT @Database_Name = DB_NAME()


/*Checking whether your Database's Recovery Model is Simple or not*/
IF NOT EXISTS (SELECT 1 FROM SYS.DATABASES WHERE Name = @Database_Name AND recovery_model_desc = 'SIMPLE')
BEGIN
    /*Changing YourDatabase's Recovery Model to Simple*/
      SET   @SQLString = 'ALTER DATABASE ' + @Database_Name + '  SET RECOVERY SIMPLE'
      EXEC (@SQLString)
END

/* Declaring the variable to store the log*/
DECLARE @LogFileLogicalName SYSNAME

/* Setting the value to store the log*/
SELECT @LogFileLogicalName=Name FROM SYS.DATABASE_FILES WHERE TYPE=1

/*Shirnking the log file*/
DBCC Shrinkfile(@LogFileLogicalName,1)


/* ALTER DATABASE YourDatabase_Name SET RECOVERY FULL */
SET @SQLString = 'ALTER DATABASE ' + @Database_Name + '  SET RECOVERY FULL'
EXEC (@SQLString)

GO



No comments:

Post a Comment