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 |
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
 Posted - 2004-09-20 : 07:46:38
|
| I have a DB that has a DB filesize of 6 gb and a transaction log filesize of 66gb!!!! This DB has a potential for alot of growth because it holds scanned documents for retention and they are in the process of scanning in several years worth of documents from what I understand. I have my maintenance plan setup to backup the DB full overnight and transaction log backups hourly during the day. usually my transaction log backups are small - but on friday, the transction log backup was 66gb and it filled up the disk. after doing some cleanup, I ran a full and then a transaction log backup and everything seems OK. But the transaction log file itself remains at 66gb! I'm just wondering how I can optimize my Database AND especially my maintenance plans so that the transaction log gets back down to a manageable size?? Can I or should shrink the transaction log?? Should my DB be set to 'autoshrink'??????? |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-20 : 09:37:18
|
| The transaction log will always be the biggest size it ever got to - it does not shrink by itself.You can manually shrink it, but if the thing that caused the 66GB transaction file runs again you'll be back to square one.Perhaps it was an index rebuild or somesuch - that easts transaction space!One answer might be to make the transaction backup run every, say, 10 minutes - that way less of the 66GB job would, hopefully, run within the 10 minute window, and thus the transaction file would be smaller.Or schedule in a SHRINK LOG task - but that periodic 66GB log file might get you a, fatal, disk full!Kristen |
 |
|
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
Posted - 2004-09-20 : 13:06:57
|
| Kristen. Thanks so much for your help. unfortunetly, I was handed this Instance after it was already in production and I'm just trying to get it cleaned up. In the maintenance plan, the 'reorganize data and index pages' was set to run once a week - I've turned that OFF. seems like when this was run, the transaction log grew to 66gb. I have a good FULL backup of the database, and I've suspended my periodic transaction log backups. Now.... I need to figure out the best way to get the transaction log shrunk down. I'm nervous about just scheduling a skrink log task because i'm not sure what will happen if it fails. Can you help with any suggestions? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-20 : 13:39:30
|
| Make a FULL backup after your shrink / truncate the log, then you should be fine.You will have to find out the logical name of the LOG file ("MyDatabase_LOG" in my example below), but the SHRINK command should be something like:USE MyDatabaseGOBACKUP LOG MyDatabase WITH TRUNCATE_ONLYGODBCC SHRINKFILE (MyDatabase_Log, 1)GOKristen |
|
|
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
Posted - 2004-09-20 : 13:50:09
|
| Worked like a champ on my test machine. Thank you so much for your assistance. |
|
|
|
|
|
|
|
|
help! LARGE transaction log filesize