T-SQL Stored Procedure to Shrink the Log File of a Database

This script is particularly useful when you are dealing with disk space limitations on dev or uat boxes when point-in-time recovery capabilities are not required and the simple recovery model is very much acceptable. I use this stored proc constantly to reduce the t-log file sizes of DB’s that are just restored from their PROD dumps.

[sourcecode language=”sql” wraplines=”false”]
/*
.SYNOPSIS
Stored procedure to shrink the t-log file for specific DB’s.

.SQL Version
2005+

.Last Modified
2014-02-27 by Nick Xu

.Example
EXEC sp__ShrinkLogFile [AdventureWorks]
*/

–For generic setting
USE master
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = N’sp__ShrinkLogFile’ AND type = N’P’)
BEGIN
DROP PROC sp__ShrinkLogFile
END
GO

CREATE PROC sp__ShrinkLogFile
@dbname VARCHAR(50) = NULL
AS
BEGIN

IF @dbname IS NULL
BEGIN
SELECT ‘Please specify the DB name that you would like to shrink…’
RETURN
END

DECLARE @sql AS VARCHAR(500)
DECLARE @logfile_name AS VARCHAR(50)

SET @sql = ‘USE master; ‘ + ‘ALTER DATABASE [‘ + @dbname + ‘] SET RECOVERY SIMPLE WITH NO_WAIT;’

SELECT @sql
EXEC(@sql)

SET @sql = ‘USE [‘ + @dbname + ‘]; ‘

DECLARE logfile_cur CURSOR FOR
SELECT name FROM sys.master_files
WHERE TYPE = 1 AND database_id = DB_ID(@dbname)

OPEN logfile_cur

FETCH NEXT FROM logfile_cur INTO @logfile_name

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = @sql + ‘DBCC SHRINKFILE(”’ + @logfile_name + ”’, 1);’
FETCH NEXT FROM logfile_cur INTO @logfile_name
END

CLOSE logfile_cur
DEALLOCATE logfile_cur

SELECT @sql
EXEC(@sql)

END
GO

[/sourcecode]