Shrinking Transaction Log Size??

Question

Post reply

Shrinking Transaction Log Size??

Jeff-67583

SSCertifiable

Points: 5625
More actions
February 10, 2004 at 9:04 am

#161614

Hi,

I have a 12 GB Transaction log file. I performed a "BACKUP LOG" command. Then a DBCC command to shrink the file. The resulting OS file size for the log file was only 200 meg smaller? Any thoughts?

THanks. Jeff

Many thanks. Jeff

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

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

sswords SSCrazy Eights Points: 8207 More actions · Answer 1

Jeff,

You didn't say what version you're using, but check the FAQ's here: http://qa.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=2&faqid=154

There are two links from there to Microsoft's site depending on whether you're using SQL Server 7 or 2000. I've used the procedure on Microsoft's site for 7 with success several times. 12 Gig's sounds pretty large for a TL file. How often are you dumping the log? Hope this helps.

My hovercraft is full of eels.

Jeff-67583 SSCertifiable Points: 5625 More actions · Answer 2

Jeff-67583

SSCertifiable

Points: 5625

February 10, 2004 at 12:42 pm

#493470

Hi,

The version is MSSQL2000. I followed the link and have previously done those steps. Do you have the links for microsoft? THe log hasn't been dumped in 6months. Its for a large call centre logging system.

Thanks. Jeff

Many thanks. Jeff

asifrashid Right there with Babe Points: 762 More actions · Answer 3

asifrashid

Right there with Babe

Points: 762

February 11, 2004 at 2:42 am

#493576

Take the TL backup, de-attach the DB, move TL file to other folder (don;t delete) and try attaching the DB without the TL file. System should create new TL file. Try this out if this can be usefull.

EdVassie SSC Guru Points: 60312 More actions · Answer 4

Two points...

1) You need to be aware of the internal structure of the transaction log, and what happens when you do a backup and when you do a shrink.

See 'Shrinking the Transaction Log' in BOL. This should explain why a SHRINKFILE of the log may not release much space if the virtual log file in use happens to be near the end of the physical file.

2) You need to be clear about what you want to achieve when you shrink the transaction log.

If you repeatedly have growth and shrink of any database file, you will end up with very bad OS file fragmentation. This fragmentation will impact the performance of your database, and can only be cured by stopping SQL Server and doing a NTFS defrag. Very very very bad OS file fragmentation can give reliability problems in NTFS.

If your transaction log has grown to a large size due to a 1-off situation, and shrinking it will give a reasonably permanent reduction of space, then a shrink is a reasonable thing to do.

However, if any space reclaimed by a shrink is likely to get allocated to the same file within a month, and you shrink again, then the repeated shrinks will give harmful OS file fragmentation. If you have been doing repeated shrinks over the last few months, then you will get a performance gain if you do a OS defrag.

If the 'natural' size of your log file gives you disk space problems, then build a business case to buy more disk. The OS fragmentation problem is part of your case. If management want to take the performance and reliability hit of bad fragmentation that is their responsibility, otherwise they will have to approve buying more disk.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.