TLOG not shrinking

Question

TLOG not shrinking

Adam Bean

One Orange Chip

Points: 26631
More actions
September 8, 2008 at 11:51 am

#123501

I know I've had this before, and I really don't remember how to resolve it ...
For some reason my TLOG backup job was disabled on one of my servers, and the TLOG grew quite large. So I re-enabled and ran, got a successful TLOG backup. Now the TLOG has 99% free, but is ridiculously to large (134gb, yikes!). I'm attempting to shrink, and nothing happens ...
DBCC SHRINKFILE (N'logfilename' , 10240)
Returns:
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
1421720812812817208128128
But does not shrink the file size.
So I tried:
DBCC SHRINKFILE (N'logfilename' , 0, TRUNCATEONLY)
Same thing.
What am I missing?! This database is in high safety mirroring, is that the cause?
* Yes, I know shrinking is bad ... but in this case, it has to be done.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

RPSQL Hall of Fame Points: 3228 More actions · Answer 1

There must be some uncommited transactions are there in your database. After you did truncate only wait some time....the transactions will roll back and it will clear some space for you...After some time shrink logs again...It will clear some stuff

Glen SSCertifiable Points: 6727 More actions · Answer 2

http://support.microsoft.com/default.aspx?scid=kb;EN-US;937531

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

Thanks guys, waited it out a bit, ran again, worked fine. Never seen that before.

Thanks

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 4

You need to fill the t-log so it rolls over to the beginning of the file,

so you may have to perform the shrink a couple of times.

Thats why this script has a timer.

-- Shrink_TrxLog.SQL

--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

Perry Whittle SSC Guru Points: 233792 More actions · Answer 5

in this scenario the best way to analyse the log status is using

dbcc loginfo

look at the status column and they should ideally be 0. The further down you scroll the more important you see 0 and not 2 as the status. Status of 2 indicates used portion of the file hence no shrink. Sometimes backing up the log a couple of times does the trick

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉