The Best Script ever on -- SHRINK LOG FILE

http://education.sqlfarms.com/education/ShowPost.aspx?PostID=117
-----------------------------------------------------------------

SQL Farms Admin :
================
Now, to help you resolve the shrink problem:
What you describe happens often if the database was created with small disk space allocation and was set to autogrow, and several autogrows indeed took place. The result of 2, instead of 0, in the DBCC LOGINFO command prevents the server from shrinking the file appropriately. This needs to be overcome by backing up portions of the transaction log, (until transaction log fully backed up) and then truncating the transaction log.

To help you in this task, I am attaching the following script, which should get you the desired result. (I had the script around for a while, and it worked for me, but although I tuned it a little, I am not sure who the original developer is, to give him creadit :) ).

Please let me know whether this helped your problem.




/**************************************************************
Input Params:
-------------
@target_percent tinyint. default = 0. Target percentage of remaining shrinkable
space. Defaults to max possible.
@target_size_MB int. default = 10. Target size of final log in MB.
@max_iterations int. default = 1000. Number of loops (max) to run proc through.
@backup_log_opt nvarchar(1000). default = 'with truncate_only'. Backup options.
*************************************************************/

-- DB parameters
DECLARE @target_percent tinyint
DECLARE @target_size_MB int
DECLARE @max_iterations int
DECLARE @backup_log_opt nvarchar(1000)

SET @target_percent = 0
SET @target_size_MB = 0
SET @max_iterations = 1000
SET @backup_log_opt = 'WITH TRUNCATE_ONLY'

DECLARE @db SYSNAME
DECLARE @last_row INT
DECLARE @log_size DECIMAL(15,2)
DECLARE @unused1 DECIMAL(15,2)
DECLARE @unused DECIMAL(15,2)
DECLARE @shrinkable DECIMAL(15,2)
DECLARE @iteration INT
DECLARE @file_max INT
DECLARE @file INT
DECLARE @fileid VARCHAR(5)
DECLARE @prev_max_iterations INT
DECLARE @command VARCHAR(500)

SET NOCOUNT ON

SET @db = db_name()
SET @iteration = 0
SET @prev_max_iterations = 2^31-1

IF OBJECT_ID('tempdb..#loginfo', 'U') IS NOT NULL
DROP TABLE #loginfo

-- This table stores the results of DBCC LOGINFO
CREATE TABLE #loginfo(
id INT identity,
FileId INT,
FileSize NUMERIC(22,0),
StartOffset NUMERIC(22,0),
FSeqNo INT,
Status INT,
Parity SMALLINT,
TimeorLSN VARCHAR(25))

CREATE UNIQUE CLUSTERED INDEX loginfo_FSeqNo ON #loginfo ( FSeqNo, StartOffset )

IF OBJECT_ID('tempdb..#logfiles', 'U') IS NOT NULL
DROP TABLE #logfiles

CREATE TABLE #logfiles (
id INT IDENTITY(1,1),
fileid VARCHAR(5) NOT NULL)

INSERT INTO #logfiles ( fileid )
SELECT CONVERT(VARCHAR, fileid )
FROM sysfiles
WHERE status & 0x40 = 0x40

SET @file_max = @@ROWCOUNT

INSERT INTO #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, TimeorLSN )
EXEC ( 'dbcc loginfo' )

SET @last_row = @@rowcount

PRINT 'Max iterations = ' + rtrim(cast(@max_iterations as varchar(20)))

SELECT @log_size = sum( FileSize ) / 1048576.00,
@unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
FROM #loginfo

SET @unused1 = @unused -- save for later

-- changed this so that it will print with rest of output SBP
PRINT '
iteration ........... = ' + cast(@iteration as varchar(10)) + '
log size, MB ........ = ' + cast(@log_size as varchar(10)) + '
unused log, MB ...... = ' + cast(@unused as varchar(10)) + '
shrinkable log, MB .. = ' + cast(@shrinkable as varchar(10)) + '
shrinkable % ........ = ' + cast(convert( DECIMAL(6,2), @shrinkable * 100 / @log_size ) as varchar(10))

-- If @target_size_MB is entered, it will take precedence
-- over @target_percent, by calculating a new @target_percent.
IF @target_size_MB > 0
SET @target_percent = (@target_size_MB / @log_size) * 100
ELSE
SET @target_size_MB = 10

-- Changed @target_percent to + 1, because many times the end result is
-- slightly larger than the target.
WHILE @shrinkable * 100 / @log_size > (@target_percent + 1)
AND @iteration < @max_iterations
BEGIN
SET @iteration = @iteration + 1 -- this is just a precaution

SET @file = 0

WHILE @file < @file_max
BEGIN
SET @file = @file + 1

SELECT @fileid = fileid
FROM #logfiles
WHERE [id] = @file

SET @command = 'DBCC SHRINKFILE( ' + @fileid + ',' + RTRIM(CAST(@target_size_MB as varchar(10))) + ')'

PRINT @command
EXEC (@command)
END

EXEC( 'BACKUP LOG [' + @db + '] ' + @backup_log_opt )

TRUNCATE TABLE #loginfo
INSERT INTO #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, TimeorLSN )
EXEC ( 'DBCC LOGINFO' )

SET @last_row = @@ROWCOUNT

-- The most iterations we really need to do is the number of logical log files,
-- and this should change if the log is shrinking. Therefore, reset
-- @max_iterations within the loop.
SELECT @max_iterations = COUNT(*)
FROM #loginfo

-- If the number of logical log files did not change from last iteration, get out.
IF @max_iterations = @prev_max_iterations
SET @max_iterations = 0
ELSE
SET @prev_max_iterations = @max_iterations

PRINT 'Max iterations = ' + RTRIM(CAST(@max_iterations as varchar(20)))

SELECT @log_size = SUM( FileSize ) / 1048576.00,
@unused = SUM( CASE WHEN Status = 0 THEN FileSize ELSE 0 END ) / 1048576.00,
@shrinkable = SUM( CASE WHEN id < @last_row - 1 and Status = 0 THEN FileSize ELSE 0 END ) / 1048576.00
FROM #loginfo


PRINT '
iteration ........... = ' + CAST(@iteration AS VARCHAR(10)) + '
log size, MB ........ = ' + CAST(@log_size AS VARCHAR(10)) + '
unused log, MB ...... = ' + CAST(@unused AS VARCHAR(10)) + '
shrinkable log, MB .. = ' + CAST(@shrinkable AS VARCHAR(10)) + '
shrinkable % ........ = ' + CAST(CONVERT( DECIMAL(6,2), @shrinkable * 100 / @log_size ) AS VARCHAR(10))
END


IF @unused1 < @unused
SELECT 'After ' + CONVERT( VARCHAR, @iteration ) +
' iterations the unused portion of the log has grown from ' +
CONVERT( VARCHAR, @unused1 ) + ' MB to ' +
CONVERT( VARCHAR, @unused ) + ' MB.'
UNION ALL
SELECT 'Since the remaining unused portion is larger than 10 MB,' WHERE @unused > 10
UNION ALL
SELECT 'you may try running this procedure again with a higher number of iterations.' WHERE @unused > 10
UNION ALL
SELECT 'Sometimes the log would not shrink to a size smaller than several Megabytes.' WHERE @unused <= 10

else
select 'It took ' + convert( VARCHAR, @iteration ) +
' iterations to shrink the unused portion of the log from ' +
convert( VARCHAR, @unused1 ) + ' MB to ' +
convert( VARCHAR, @unused ) + ' MB'


-- cleanup
IF OBJECT_ID('tempdb..#loginfo', 'U') IS NOT NULL
DROP TABLE #loginfo

IF OBJECT_ID('tempdb..#logfiles', 'U') IS NOT NULL
DROP TABLE #logfiles
GO