Urgent: Transaction Log Issue...

[AZSEEK]

We have a SQL Server 2000 database with a 3.7 GB database... We normally perform a backup of the database and transaction log on a daily basis -- over the past three nights the transaction log has been failing due to its running out of disk space -- and we have over 20 GBs of space available...

Is there any way to purge the transaction log - been reading about check points and stuff -- Help

 

[JayKusch]

Here is a quick script that will shrink that Tlog down



CREATE PROCEDURE usp_Shrink_Transaction_Log
@Target_Percent TinyInt = 0,
@Target_Size_MB Int = 1,
@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

 

[ram1975]

If the harddisk is full and you are unable to take the logbackup, right click on the database, select properties, select options tab and change the Recovery model to &quot;Simple&quot; and say ok. Again right click on the databased and select shrinkdatabase. I hope this should solve your problem.