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.

/*
.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

Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in SQL Server, T-SQL Script, Transaction Log and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s