If you have been in the position of supporting SQL Server and having to move database backups around at any point, you have probably become aware of how troublesome large log files can make the process. This article is intended to offer you a quick solution to the problem.

Prior to commencing any work on the inner workings of a database always ensure you back it up FIRST!

After backing up the database, follow the steps below to truncate the log file of your database to an acceptable size:

1. Open your management tool of choice (Enterprise Manager/SQL Query? Analyzer? for most) and select the database you wish to work on.
2. Copy and paste the script below into your management tool:
3. Change? ‘SET @ShrinkToMb = 2′ in the script so that the number 2 represents the number of Mb you wish the script to truncate your log file to.
4. Run the script.
5. Backup your database.

DECLARE @LogFileName NVARCHAR(100)
,@LogFileSize INT
,@ShrinkToMb INT
,@DBName NVARCHAR(200)

SET @ShrinkToMb = 2

SELECT @LogFileName = LTRIM(RTRIM([name]))
,@LogFileSize = [size]
,@DBName = DB_Name()
FROM sysfiles
WHERE? groupid = 0

IF((@ShrinkToMb * 128) < @LogFileSize)
BEGIN
DBCC SHRINKFILE(@LogFileName, @ShrinkToMb)
BACKUP LOG @DBName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(@LogFileName, @ShrinkToMb)
SELECT @LogFileSize = [size]
FROM sysfiles
WHERE groupid = 0
PRINT @DBName + ‘ log file (’+@LogFileName+’) shrunk to ‘
+ cast(@LogFileSize AS NVARCHAR)
+ ‘ pages, you requested a size of ‘
+ cast(@ShrinkToMb * 128 AS NVARCHAR)
+ ‘ pages’
END
ELSE
BEGIN
PRINT @DBName + ‘ log file (’+@LogFileName+’) is smaller than ‘
+ cast(@ShrinkToMb * 128 AS NVARCHAR)
+ ‘ pages already - No action taken’
END

This tip was brought to you by the staff of Techita.

This entry was posted on Monday, April 16th, 2007 at 9:40 pm and is filed under SQL Server 2000. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.