Log file not shrinking

[divinyl]

Hi all

Having an issue with a log file for one of my test databases. In attempt to increase the size of the database to a few hundred gig, I have been doing a self join insert on a table. This causes my log file to increase massively - so i normally have to cancel the insert and then truncate and shrink the log - which has worked so far. It's now grown to 13 gb and is not shrinking at all. I've backed up the log with teh runcate_only option, and ran a dbcc shrink statement but it's staying 13 gb!!!! Does anyone know how i can shrink it down?

Any help appreciated!

Thanks,
Div

 

[ptheriault]

I use this log shrinking script. You have to set the size you want and the name of your log file.

--select * from sysfiles
--drop table DummyTrans
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT

-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***
--USE -- This is the name of the database for which the log will be shrunk.
SELECT @LogicalFileName = ' ', -- Use sp_helpfile to identify the logical file name that you want to shrink.
@MaxMinutes = 2, -- Limit on time allowed to wrap log.
@NewSize = 10 -- 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

 

[mrdenny]

You also need to be sure that all the entries in the transaction log have been checkpointed. If they aren't checkpointed you won't be able to shrink the file passed the last uncheckpointed transaction.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[divinyl]

Thank you Ptheriault for this script - it has worked a treat!!

About the checkpoints..hmm, i'll need to do more reading up on this, i.e. figure out when things get checkpointed and if one can actually run some sort of statement that will checkpoint entries in the log..

Thanks all

Div

 

[mrdenny]

That statement would be:

CHECKPOINT

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com