Looking for a Truncate Log Script?

[Maritime]

I'm running SQL 7.0 and I posted before about a couple of my databases the seem to grow out of control. On each database, I have set the truncate log on checkpoint option, however because, I have so many jobs that need to run overnight I'm thinking that the logs might not have enough time to clear. At first I tried scheduling a few shrink database jobs in between my copy table jobs but ended up with a corrupt database. I have found that sometimes when the disk space fills up, running a truncate log command frees up the space. Therefore I'd like to know is there a way to create a truncate log job that I can schedule to run in between my copy table jobs. Basically, I'm looking for a truncate log script from which I can create a scheduled job.

Can I use 'backup log db_name with truncate_only'.
Thanks in advance?

 

[JayKusch]

Here ya 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


Thanks

J. Kusch

 

[Maritime]

Hi just a small question? What's wrong with using 'backup log db_name with truncate_only' option?

 

[JayKusch]

Noting at all ... the script just generates some ancillary info after it is run that our DBAs wanted to see. For a quick and dirty, your options is the way to go!

Thanks

J. Kusch