September 15, 2008 at 10:37 am
There are a lot of posts regarding this issue and I have read a ton of them but am still stuck. I have a number of databases that have large transaction logs that are only using a fraction of their allocated space. One in particular is about 6GB but never uses more than 200MB. It is backed up hourly by BackupExec. When I try to execute DBCC SHRINKFILE immediately after the log file is backed up I recieve:
Cannot shrink log file 2 (Some_Log) because all logical log files are in use.
I then execute DBCC LOGINFO and there are 235 virtual logs files. The last four and the first three of which are active (status = 2). So that explains why I can't shrink the logfile using DBCC SHRINKFILE. The million dollar question is how I get those last virtual log files to not be active? Every log file I try to shrink can't be shrunk because the last virtual log files are being used. I took over a mess at this company and I have really always been more of a DB developer than a DBA. Not sure how to address this. We do nightly db backups and hourly tran backups using BackupExec. But the tran logs for all DBs were allowed to get very large with a large number of virtual tran logs.
Thank you very much for any help!
Chris
September 15, 2008 at 10:54 am
Have you tried running CHECKPOINT against the db before DBCC SHRINKFILE?
_____________________________________________________________________
- Nate
September 15, 2008 at 11:06 am
Because of the way sqlserver log files work, you can only shrink up to the last chunck in use.
Considering the error you got, I'd say enlarge the log with a couple MB so you know there is free space to handle the checkpoint.
--INF: How to Shrink the SQL Server 7.0 Transaction Log
-- SQL7 http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD
-- SQL7 http://www.support.microsoft.com/kb/256650
-- SQL2000 http://support.microsoft.com/kb/272318/en-us
-- SQL2005 http://support.microsoft.com/kb/907511/en-us
-- select db_name()
-- select * from sysfiles
-- THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL and the urls !!
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***
SELECT @LogicalFileName = 'logicalname', -- Use sp_helpfile to identify the logical file name that you want to shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 100 -- 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
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 15, 2008 at 12:11 pm
Do you mean you have 235 log files?
September 15, 2008 at 12:58 pm
No. One physical log file for that database but it consists of 235 virtual log files. I can't shrink the log file because the virtual log files toward the end of the log are active.
Tried CHECKPOINT. No dice. Nothing works because the last log chunk is perpetually active. I will probably try the script above later. Thanks for posting that.
September 15, 2008 at 2:46 pm
MOST OF THE CASE YOU WILL BE ABLE TO TRUNCATE THE LOG AFTER YOU STOP AND RESTART SQL SERVICES. BUT IF IT IS IN PRODCTION ENVIRONMENT YOU MAY NOT BE ABLE TO DO THAT BECAUSE OF DOWN TIME. IN THIS CASE YOU CAN DO
DBCC OPENTRAN
( { 'database_name' | database_id} )
[ WITH TABLERESULTS
[ , NO_INFOMSGS ]
]
tO SEE THAT ANY OPEN TRANSACTION EXIST IN YOUR DATABASE FOR A QUITE LONG TIME THAT MAY PREVENT YOU TO SHRINK THE FILE.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply