January 27, 2005 at 12:48 am
Hi All
Server : sql server 7.0 windows 2000
i am using sql backup jobs at 3 am ( full ) and trn log backup at 12:00 amd and 12:pm daily.
there are around 80 db's of not very big size. ( 50 -200 MB)
it has been observed over a period of last 8 months that some of database log files are being backed up as other in database maintenance plan but the size of those files is not reducing like locakdb6 has trn log file size of 300 MB an dother has 200 MB
when oberserve there no sleeping/open connections on these dbs' from application side.
also i am using simple database maintenance plan to run backup jobs for both full and trn log backup
please help that what can be causes of this behaviuor and how to fix it permanenently
in database options : onlky default opyions are sleected truncate log on check point is "not" selected.
Please advise ..
Many Many thks in advance
Regards
Deeps
January 27, 2005 at 8:40 am
Hi Deep,
If I understand your question it's regarding the physical size of the transaction log files as opposed to the size of the transaction log backups generated. It's good that you have a maintenance plan to perform the backups but this is a different thing to shrinking the log file.
Here is the query that i use for shrinking the log file, i don't claim to have written it but i can't find the original one and the author information has been stripped out of this (appologies to the original author). You may find that you have to run it twice because it doesn't always work on the first time through, i had thought about looking into why but to be honest I have too much to do and it always works on the second go so it's not that high up on my list of priorities.
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***
USE [your database] -- This is the name of the database for which the log will be shrunk.
SELECT @LogicalFileName = 'yourdatabase_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 = 5 -- 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
Dont forget to do what it says at the end, do a full database backup to guarentee you have a restore point after the shrinking.
January 27, 2005 at 9:36 am
Yes, this is the best script to fix permanenently log size. I use it as well for long time (this script is from Microsoft). Also, I suggest you to do logs backups more often, than twice a day. Try to find out if your logs grow to this sizes daily or just on the day when optimisation job runs (from maintenance plan).
January 27, 2005 at 9:39 am
oops, missed the bit that mentioned only 2 transaction log backups a day, I'd go with one every hour on a production system.
January 28, 2005 at 6:46 am
I try to keep the total size of a transaction log to 10-20% of the total database data size for starters and hourly transaction log backups. Then based on log size and update frquency of the database I fine tune from there (yes that means monitoring things). For transaction logs on databases of your size (the largest being 200 MB) a transaction log should probably be no larger than 40 Mb. If they grow larger than that I'd look to you SQL maintenance plans and check to see that it's not 'reorganizing the data and index pages' of your database on a daily or weekly basis. For databases this small, that function may only be needed once or twice a year (unless there is a large turnover of the data, say >20% per day. I would suggest sampling 100% of the data in the maintenance plan sice the databases are very small.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
January 31, 2005 at 12:24 am
Hi All
Thanks for all inputs
alternatively i tried dbcc shrinkfile and yes it has to be run twice and i oberved teh trn log file reduced to mentioned size.
but question her is
1) is it must t run to shrink file size of trn log files.
Or
2) as said shall i do more frequent trn log back say 4 hrly and the size will keep on reducing
3) as i obeserved the trn log file size of the few databse was 400 Mb for long time like almost an year .
Please help
Regards
Deeps
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply