September 23, 2008 at 9:00 am
We're running out of disk space and trying to recover some by running the Shrink Database/Log File command. It's not shrinking the log file and only seems to increase the space used for the log file. What options on the shrink command are best to use? I've tried it with the default of "Compress pages and truncate free space" and also "Truncate free space from the end of the file" and neither one shrinks the log file. We have been able to shrink the databases somewhat but most of that space is taken up b y the increase in the log file size. We have 2 log files that are over 50GB in size and the databases are 6Gb and 24Gb. We have stopped the SQL Services and rebooted the server and that didn't do anything. Any ideas.
September 23, 2008 at 9:07 am
andrew.critelli (9/23/2008)
We're running out of disk space and trying to recover some by running the Shrink Database/Log File command. It's not shrinking the log file and only seems to increase the space used for the log file. What options on the shrink command are best to use? I've tried it with the default of "Compress pages and truncate free space" and also "Truncate free space from the end of the file" and neither one shrinks the log file. We have been able to shrink the databases somewhat but most of that space is taken up b y the increase in the log file size. We have 2 log files that are over 50GB in size and the databases are 6Gb and 24Gb. We have stopped the SQL Services and rebooted the server and that didn't do anything. Any ideas.
Most chances that the databases are in full recovery model. If this is true, you should backup the log and after backing up the log, you’ll be able to shrink the log. I suggest that you’ll have a look in books on line on the topic – “Overview of the Recovery Models” to better understand the implications of the subject and also you’ll be able to make a decision if you want to backup your log or modify the recover model to simple.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 23, 2008 at 9:53 am
Thanks Adi. I'll take a look. We're also working with our SQL Server DBA and he was he issued backup transaction with truncate_only to clear out some space.
September 23, 2008 at 9:56 am
Also have a read through this for a quick overview of how recovery models affect the tran log:
http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply