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