Truncate Transaction - All Databases

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go


--sample running 
-------shrink all databases
-----------EXEC SP_MSFOREACHDB 'sp_force_shrink_log_db 0,100,1000,''with truncate_only'',''?'''
--shrink only the test database
--sp_force_shrink_log_db 0,100,1000,'with truncate_only','test'
ALTER PROC [dbo].[sp_force_shrink_log_db]
 @target_percent TINYINT = 0,
 @target_size_MB INT = 100,
 @max_iterations INT = 1000,
 @backup_log_opt NVARCHAR(1000) = 'with truncate_only',
 @DB VARCHAR(30)
AS 
 SET nocount ON

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

 SELECT 
 @iteration = 0
 PRINT @DB
 CREATE TABLE #loginfo
 (
 id INT IDENTITY,
 FileId INT,
 FileSize NUMERIC(22,0),
 StartOffset NUMERIC(22,0),
 FSeqNo INT,
 Status INT,
 Parity SMALLINT,
 CreateLSN NUMERIC(30)
 )



 CREATE TABLE #logfiles
 (
 id INT IDENTITY(1,1),
 fileid VARCHAR(5) NOT NULL
 )
 INSERT #logfiles
 ( fileid )
 SELECT CONVERT(VARCHAR,fileid)
 FROM sysfiles
 WHERE status & 0x40 = 0x40 
 SELECT @file_max = @@rowcount

 IF OBJECT_ID('table_to_force_shrink_log') IS NULL 
 EXEC
 (
 'create table table_to_force_shrink_log ( x nchar(3000) not null )'
 )

 INSERT #loginfo
 ( FileId,FileSize,StartOffset,FSeqNo,Status,
 Parity,CreateLSN )
 EXEC (
 'dbcc loginfo (' + @DB + ')'
 )

 SELECT @last_row = @@rowcount
 PRINT @last_row
 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

 SELECT @unused1 = @unused -- save for later

 SELECT 'iteration' = @iteration,
 'log size, MB' = @log_size,
 'unused log, MB' = @unused,
 'shrinkable log, MB' = @shrinkable,
 'shrinkable %' = CONVERT(DECIMAL(6,2),@shrinkable
 * 100 / @log_size)

 WHILE @shrinkable * 100 / @log_size > @target_percent
 AND @shrinkable > @target_size_MB
 AND @iteration < @max_iterations
 BEGIN
 SELECT @iteration = @iteration + 1 -- this is just a precaution

 EXEC
 (
 'insert table_to_force_shrink_log select name from sysobjects delete table_to_force_shrink_log'
 )

 SELECT @file = 0
 WHILE @file < @file_max
 BEGIN
 SELECT @file = @file + 1
 SELECT @fileid = fileid
 FROM #logfiles
 WHERE id = @file
 EXEC
 (
 'use ' + @DB
 + '; dbcc shrinkfile( '
 + @fileid + ' )'
 )
 END

 EXEC
 (
 'backup log ' + @db + ' '
 + @backup_log_opt
 )

 TRUNCATE TABLE #loginfo 
 INSERT #loginfo
 ( FileId,FileSize,StartOffset,
 FSeqNo,Status,Parity,CreateLSN )
 EXEC (
 'dbcc loginfo'
 )
 SELECT @last_row = @@rowcount

 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

 SELECT 'iteration' = @iteration,
 'log size, MB' = @log_size,
 'unused log, MB' = @unused,
 'shrinkable log, MB' = @shrinkable,
 'shrinkable %' = CONVERT(DECIMAL(6,2),@shrinkable
 * 100 / @log_size)
 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'

 EXEC (
 'drop table table_to_force_shrink_log'
 )