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 |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2010-03-17 : 06:54:27
|
| Hi,it been at least 5 years since I last worked with a sql server 2000 and never as an administrator so I have to ask about a problem I had with a client today. They had a database in simple recovery model and the log file was set at 1GB with 22GB of free space on the disk. There were 7-8 other databases on the server but they were all less than a GB each and had it's files on different drives so they weren't interfering.Now the problem was that the event log reported errors almost every night about the translog for this given database being full, but filegrowth for the logfile was set to unlimited at 10% and with 22GB free on the disk this didn't make sense to me at all. Does anyone have a reasonable explanation for this?- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-17 : 07:13:42
|
| Index Rebuild scheduled task creating some massive single transaction perhaps?I would not set LOG to percentage growth, most especially on SQL 2000 where the file growth initialisation can be deadly slow, especially on a busy server. I recommend changing to some, reasonable, fixed MB amount.Perhaps there is some AutoShrink (Shock!Horror!) scheduled task (or maybe there is an Option that you can tick for that?) which is re-shrinking the Log such that you appear to have 22GB free when you are checking? |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-03-17 : 10:37:58
|
Now that you mention it, there was in fact an auto-shrink enabled on the database which I removed but it didn't occur to me that this might be the reason why the log was so small when I looked at it. The reason why I didn't consider this to be relevant was because the log-file was 1GB and I would have expected it to shrink down to something much less than that. After thinking more about it I guess it doesn't shrink more than that of the initial file size which makes sense I guess. Thank you so much for shedding light in my room of darkness here Kristen, it was all just a matter of adding two and two together (which can be a real challenge to some of us sometimes!!)  - LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-17 : 11:49:56
|
| I'm glad to have been of some use |
|
|
|
|
|
|
Log file growth on db with simple recovery model