February 10, 2004 at 9:04 am
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
February 10, 2004 at 12:32 pm
Jeff,
You didn't say what version you're using, but check the FAQ's here: http://www.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.
February 10, 2004 at 12:42 pm
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
February 11, 2004 at 2:42 am
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.
February 11, 2004 at 3:00 am
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.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 11, 2004 at 4:58 am
Try ALTER DATABASE MODIFY FILE (name=_Log, size=) and then
February 11, 2004 at 5:02 am
Sorry, try
ALTER DATABASE DBName
MODIFY FILE (name=DBName_Log, size=IncreasedSizeInMB)
and then
DBCC SHRINKFILE (2, DesiredSizeInMB)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply