Large Transaction Log

  • I have concerns on the size of a transaction log for a database. The database was created and is somewhat maintained by an outside group. So the changes that I can make to the database are limited. I'm certain that they have long running transactions that would increase the log size. But the log hasn't appeared to grow any in the last week.

    We use SQL Server 2000 Enterprise edition. The database recovery model is simple with the data file and transaction log file set to auto grow without any limitations. The database is backed up once per day. Additionally, the SQL service has been stopped twice in the last week for environmental reasons (cooling system failure in server room).

    The data file size is 490 MB and the Log file size is 13,138 MB with 97% used. Call me silly but that transaction log seems a little too big for this small database.

    I've checked to see if the log contains open transactions and it doesn't. Since the log is using almost all the space, I don’t think that shrinking it will help.

    Any suggestions on how can I reduce the size of the log? Or should I even care about the size? Currently I'm not in any danger of running out of disk space, but auto grow with no limit makes me uneasy.

  • You thought about running BACKUP LOG <DB> WITH NO_LOG and then running DBCC SHRINKFILE(' <logname> ', truncateonly) ?



    Shamless self promotion - read my blog http://sirsql.net

  • Yes, I've thought about trying that. 

    Will it really help? I thought that since checkpoints have been issued it would have performed a log truncation already, especially when the server was shutdown.

    But maybe I just don't understand. 

    Thanks for your help.

  • It should make a big difference, log truncation does not always happen even after checkpoints have been issued. I know it's what you would expect but in the past it's never worked, that's why I use the backup log statement.



    Shamless self promotion - read my blog http://sirsql.net

  • Thanks. I'll give it a try tonight.

  • Try this one.....

    SET NOCOUNT ON

    DECLARE @LogicalFileName sysname,

    @MaxMinutes INT,

    @NewSize INT

    -- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***

    USE [epo_cddah-epo] -- This is the name of the database

    -- for which the log will be shrunk.

    SELECT @LogicalFileName = 'epo_cddah-epo_log', -- Use sp_helpfile to

    -- identify the logical file

    -- name that you want to shrink.

    @MaxMinutes = 2, -- Limit on time allowed to wrap log.

    @NewSize = 50 -- in MB

    -- Setup / initialize

    DECLARE @OriginalSize int

    SELECT @OriginalSize = size -- in 8K pages

    FROM sysfiles

    WHERE name = @LogicalFileName

    SELECT 'Original Size of ' + db_name() + ' LOG is ' +

    CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +

    CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'

    FROM sysfiles

    WHERE name = @LogicalFileName

    CREATE TABLE DummyTrans

    (DummyColumn char (8000) not null)

    -- Wrap log and truncate it.

    DECLARE @Counter INT,

    @StartTime DATETIME,

    @TruncLog VARCHAR(255)

    SELECT @StartTime = GETDATE(),

    @TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'

    -- Try an initial shrink.

    DBCC SHRINKFILE (@LogicalFileName, @NewSize)

    EXEC (@TruncLog)

    -- Wrap the log if necessary.

    WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired

    AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk

    AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.

    BEGIN -- Outer loop.

    SELECT @Counter = 0

    WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))

    BEGIN -- update

    INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.

    DELETE DummyTrans

    SELECT @Counter = @Counter + 1

    END -- update

    EXEC (@TruncLog) -- See if a trunc of the log shrinks it.

    END -- outer loop

    SELECT 'Final Size of ' + db_name() + ' LOG is ' +

    CONVERT(VARCHAR(30),size) + ' 8K pages or ' +

    CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'

    FROM sysfiles

    WHERE name = @LogicalFileName

    DROP TABLE DummyTrans

    PRINT '*** Perform a full database backup ***'

    SET NOCOUNT OFF

  • Thank you stacenic and jadowse. The transaction log is now at an acceptable size.

  • The Transaction log gets truncated when a check point occurs only if the "Trunc. log on chkpt." option on the database is set. If this option for the database is not turned on the Transaction log entries are not deleted unless the transaction log backup is done.

    This database option can be set/unset from Entriprise Manager or executing the following queries from Query Analyser

    USE master

    EXEC sp_dboption <db_name>, 'trunc. log on chkpy', 'TRUE'

    Thanks,

    Vani.

  • Vani, 

    The option is already set, but thanks for the idea.

    Like stancenic mentioned in his/her post, the truncate on checkpoint doesn't always seem to happen like one would expect.

    Thanks again for everyone's help.

    TD

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply