Transaction Log lots of free Space

Question

Transaction Log lots of free Space

terry999

SSCertifiable

Points: 5036
More actions
November 4, 2014 at 8:19 am

#312685

Hi I have a 3rd party vendors DB on full recovery with regualr full backups and tran log backups.
I removed the shrinkDB from the daily full backup plan because of the index fragmentation problems it causes.
There is an idea that because shrinkDB is not running, the transaction Log is growing too large.
Currently the data file is 50% free space of Total 9GB, an archive operation (huge delete) is periodically performed on it. The Tran log Now is about 3GB about 95% free space. I think the tran log is getting to 3GB because of this archive operation i.e. huge delete. However it is steady at 3GB
My attitude is to leave it as it is, eventually the 9GB will be used up, it grew from original 6GB in reasonable chunks so the disk is not fragmented. MS SQL server needed at 3GB tran log (grown from 1GB) at one stage so why make it grow again if we always shrink it
Will a large Tran log which is almost all free space affect performance as opposed to a smaller file with less free space?
There are quite alot of VLFs, so I guess it could do with a shrink and a resize to reduce the number of VLFs.

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

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

daniel.freedman 80164 Mr or Mrs. 500 Points: 535 More actions · Answer 1

If you're backing up the transaction log then SQL Server should start reusing the space in the log file after the backup has finished. You could shrink the log file separately from the database file but as you say it'll only grow again and if you don't desperately need the disc space for something else then I wouldn't bother.

It's actually less efficient to have a smaller transaction log file because it takes time to grow it and that'll delay your operation while it's grabbing more disc space.

Not sure what you mean by VLF's though.

Michael L John One Orange Chip Points: 27053 More actions · Answer 2

Kimberly Tripp describes VLF's in detail in this article

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

daniel.freedman 80164 Mr or Mrs. 500 Points: 535 More actions · Answer 3

Thanks for that, I'll have a look at those articles later. Still had to do some googling to find out what VLF stands for though, as she doesn't give a definition anywhere in the article!

terry999 SSCertifiable Points: 5036 More actions · Answer 4

Thanks Daniel that's what I was thinking. Virtual Log Files, see the article

Michael that's where I read about VLFs, don't pretend to understand everything, but I can see our tran log has grown in too small increments. Perhaps it's the same article or linked to Kimberley's shows performance update/inserts slows if VLFs are too small. I couldn't resize it as last VLF was still active, but I could now as active part has rolled around.