Backup and transaction log growth
-
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. I'm just wondering how I can optimize my Database AND my maintenance plan so that the transaction log gets back down to a manageable size?? Can a shrink the transaction log?? Should my DB be set to 'autoshrink'???????
-
I wouldn't recommend autoshrink as this could happen at inconvenient times of the day, killing performance. You could setup a SQL Agent job to run DBCC SHRINKFILE at a period of low activity (although this may not get to run before your disk space disappears).
However I would be more concerned about what caused the TLOG to grow to 66GB in one day? Do you have anymore information?
Chris
-
Well, It turns out that the growth spurt in the transaction log filesize seems to have happened after 'Reorganize Data and Index Pages' ran in a maintenance plan. I've turned this off in my maintenance plan. I understand now that once the transaction log grows, it will never shrink unless you manually shrink it. Thanks to a reply to my post in a different forum, someone suggested I perform the following:
USE MyDatahase
GO
Backup Log MyDatabase WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (MyDatabase_Log, 1)
GO
I'll probably do a full DB Backup before and after.
Since they are scanning Documents like crazy and this DB has a potential to grow signifigantly, I guess I need to fiigure out how best to setup and maintain this DB.
Please tell me if any of this makes sense
and any suggestions are more than welcome! -
Your script looks good although the TRUNCATE_ONLY breaks the log sequence but then you've coped with that by backing the db up immediately afterwards.
Maybe add the DBCC SHRINKFILE as an extra step to the SQL Server agent job which backs up the transaction log?
Don't ditch the index & data page reorganisation. We schedule ours for a weekend immediately followed by a full backup.
-
awesome! Thank you so much for your help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply