Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tincupal
Starting Member
24 Posts |
 Posted - 2008-09-30 : 13:37:53
|
| We have a server with SQL 2000 hosting about 30 small databases. We ran out of disk space because a 500mb database had a log file that was 36 GB. I read the various threads and still don’t understand this log file backup and truncate/shrink issue.We run a nightly maintenance plan that rebuilds indexes, Check Data and Index Linkage and does a database backup and verify.I started backing up the log files but it didn’t shrink the file so I stopped.I'm not a SQL expert by any means, but logic would indicate if all log records have been posted to the database and especially if you backup the database then you should be able to truncate and shrink the log file. Why is this not part of a SQL maint plan? Why should we let a 500mb database grow a log file to 36gb? I'm really missing something here.Is there a command to make sure all log records have been posted to the database so then we can truncate & shrink the log file?Thanks, Al |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-30 : 14:06:02
|
| Shrinking a database file can cause performance issues. You should only do it if you know the database does not need that space anymore.But you shouldn't be truncating and shrinking all of the time, ever. Either change your recovery model to simple (if you don't require point-in-time recovery) or start backing up your transaction log (available in a maintenance plan) more frequently. We require point-in-time recovery due to the criticality of our data. We backup our transaction logs every 15 minutes.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
|
|
|
Shrinking Log File