April 1, 2004 at 8:28 am
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.
April 1, 2004 at 8:52 am
You thought about running BACKUP LOG <DB> WITH NO_LOG and then running DBCC SHRINKFILE(' <logname> ', truncateonly) ?
April 1, 2004 at 9:21 am
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.
April 1, 2004 at 9:55 am
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.
April 1, 2004 at 10:35 am
Thanks. I'll give it a try tonight.
April 2, 2004 at 1:25 am
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
April 2, 2004 at 5:20 am
Thank you stacenic and jadowse. The transaction log is now at an acceptable size.
April 6, 2004 at 3:25 am
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.
April 6, 2004 at 6:21 am
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