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 |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2004-12-07 : 19:59:29
|
I've been experiencing heavier traffic lately on my db server and its caused my logfile to grow a great deal. My harddrive has now filled up 3 times in the past 3 days , which caused indexes to corrupt the first 2 times, but I got lucky on the third and it was relatively easy to fix.I didnt see HD space as an issue as we always had lots, and I thought it was defiantely fixed when I increased my job that shrinks the logfile from weekly to daily, however it filled again. I have now changed it to run every 4 hours so I don't end up making that mistake again.Job: DBCC SHRINKFILE(databaseName_log, 2)BACKUP LOG databaseNameWITH TRUNCATE_ONLYDBCC SHRINKFILE(databaseName_log,2)Any recommendations on how I should handle this? If I am running this job should I also be backing up before I do this? Is there a way to limit the log size file? If so should I do this? I usually have about 5 gig free and I noticed that was the size of the transaction log when the HD filled up for hopefully the last time.Thanks again for your help,Mike123 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-07 : 20:10:08
|
| Yes, you can limit the log file size. Set it to the largest size you can fit and TURN OFF AUTOGROW. Since you're truncating the log every four hours I assume you're not gonna bother with log backups, therefore you might as well change to simple recovery mode. DON'T keep shrinking the log file, it will just grow again. |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2004-12-07 : 20:29:15
|
| Hi Rob,thanks for the tip, I'll be sure to switch my routine to what you recommend sounds like a better solution.I did just backup my database however and notice its about TEN times the regular size ( 7.5gig) when backed up.Could this be due to some type of corruption ?? If so what can I do to check ? Thanks again,mike123 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-07 : 20:45:34
|
A full database backup will contain log entries made during the backup process, but even so that does sound excessive. If you did a reindex or CHECKDB with repair that could account for the size.You can run DBCC CHECKDB, but if it tries to repair something that will...what a shocker...fill the log with new crap.  I'd suggest you try what Nigel does, restore the backup to another server and run the integrity checks there. If there are problems you can at least narrow it down to only the affected tables.You may want to look at adding some filegroups to this database. It will give you the option of separating data from indexes, for example, or high-update tables from lower ones. You're probably getting some fragmentation in there that's making the situation worse. Are you using text or image columns by any chance? Storing pictures in a DB? That's another place where separate filegroups could help. |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2004-12-08 : 07:15:27
|
| Hi Rob,I did both a reindex and a checkdb actually your right. What can I do to get the DB back to its normal size? I use minimal text columns and no images in the db.I have set my max size for the logfile to 2000mb autogrow file, will it error out at 2000mb or somehow make room? Is this a good idea? I'll have to read up on filegroupsThanks for the tips!mike123 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-08 : 07:47:59
|
| Yes, if autogrow is off and the log fills up, you'll get a "log full" error of some kind. Still, this is better than running out of disk space and corrupting the DB. If you haven't set a max size yet, do it ASAP.If you've completed your reindexing and repairing, you might want to try DBCC SHRINKFILE...WITH NOTRUNCATE. This won't actually shrink the file size, you can do that later though. Warning: you guessed it, the log will fill, so you might want to do it in stages. It can also take quite some time. The benefit is that it will move the data pages to the front of the file. Although I think it might be better to set up new filegroups first, and move the indexes to them, before you do the shrink. It should speed things up and lessen the burden on the log. |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2004-12-10 : 05:33:39
|
| thanks rob! think i cleared it up :) |
|
|
|
|
|
|
|
|
Hard drive filling up