Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Server 2000 Transaction Log Build up 3

Status
Not open for further replies.

AndyLee100

Technical User
Jun 26, 2003
174
GB
Hi

I work for a company that distribute a SQL Server based visual basic application. Full backups are run every night from this program using a TSQL command line. The problem is that one customers transaction log file is not automatically shrinking and has now grown to a size of 6GB. The database is set to Auto Shrink but does not seem to be. How can we stop the trans. log getting so big without having to manually shrink the database.

Any help would be greatly appreciated

Thanks

Andy Lee
 
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
 
To keep the transaction log from growing perhaps you should set up transaction log backups through the day. This is generally better for recovery reasons too. Full backups do not backup the transaction log and therefore it will continue to grow until you run out of space. Shrinking the log without backing it up first or truncating the log manually will not free up the space. And people get cranky at redoing an entire day's worth of transactions if you ever have to recover.
 
Do the following which will defenitely solve your problem.
1. Right click on the database and select properties.
2. In the options tab set the Recovery model of the database to &quot;Simple&quot;.

This will allow your database log to shrink automatically. Also make sure auto shrink is on also.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top