You can run a script similar to this one after the backup completes.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE usp_Shrink_Transaction_Log
@Target_Percent TinyInt = 0,
@Target_Size_MB Int = 10,
@Max_Iterations Int = 1000,
@Backup_TLog_Opt nVarChar(1000) = 'with truncate_only'
AS
SET NOCOUNT ON
DECLARE @DB sysname
DECLARE @Last_Row Int
DECLARE @Log_Size Decimal(15,2)
DECLARE @UnUsed_1 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)
SELECT @DB = db_name(),
@Iteration = 0
Create Table #TLog_Info (
ID Int Identity,
FileID Int,
FileSize Numeric(22,0),
StartOffset Numeric(22,0),
FSeqNo Int,
Status Int,
Parity SmallInt,
CreateLSN VarChar(50)
)
CREATE UNIQUE CLUSTERED INDEX loginfo_FSeqNo ON #TLog_Info ( FSeqNo, StartOffset )
Create Table #Tlog_Files (
ID Int Identity(1,1),
FileID VarChar(5) NOT NULL )
INSERT #Tlog_Files ( 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 #TLog_Info ( 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 #TLog_Info
SELECT @UnUsed_1 = @UnUsed -- save for later
SELECT 'Iteration' = @Iteration,
'TLog Size, MB' = @Log_Size,
'Unused TLog, MB' = @UnUsed,
'Shrinkable TLog, 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 #Tlog_Files
WHERE ID = @File
EXEC( 'DBCC ShrinkFile( ' + @FileID + ' )' )
END
EXEC( 'Backup Log [' + @DB + '] ' + @Backup_TLog_Opt )
TRUNCATE TABLE #TLog_Info
INSERT #TLog_Info ( 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 #TLog_Info
SELECT 'Iteration' = @Iteration,
'TLog Size, MB' = @Log_Size,
'Unused TLog, MB' = @UnUsed,
'Shrinkable TLog, MB' = @Shrinkable,
'Shrinkable %' = CONVERT( Decimal(6,2), @Shrinkable * 100 / @Log_Size )
END
IF @UnUsed_1 < @UnUsed
SELECT 'After ' + CONVERT( VarChar, @Iteration ) +
' iterations the unused portion of the log has grown FROM ' +
CONVERT(VarChar,@UnUsed_1 ) + ' 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, @UnUsed_1 ) + ' MB to ' +
CONVERT( VarChar, @UnUsed ) + ' MB'
EXEC( 'DROP TABLE table_to_force_shrink_log' )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks
J. Kusch
