June 8, 2011 at 10:41 am
I noticed one of our database was rapidly growing and came across a reorg/rebuild job that is being executed once a week. I assume this is the culprit, but I am not sure the best approach on resolving the issue. The database is oversize by about 12gb, so I could shrink the db then defrag or just leave it and let it eventually grow to the current size. What would be the best practice approach to this issue? Any help will be greatly appreciated.
June 8, 2011 at 10:47 am
I'll assume you are talking about the log file(s).
In that case yes the reorg/rebuild is the culprit.
I personally only work the indexes that need work so that shot down the work by more than 95% in my case (8 - 10 GB / day to avg 200 MB).
Check out the script I'm using :
http://sqlfool.com/2010/04/index-defrag-script-v4-0/
Once you have that in place you can shrink the logs back to 1-2 GB (I use 2X the biggest table).
From there on out if it grows again just leave it as is. It's because it needs to be that size.
1 last thing is to have regular log backups (which if ran during the reorg could have kept that in check a little bit).
I use 15 minutes intervals 24 / 7 cause we can afford to lose that amount of data. If you need more safety than that for DR then high availibility is probably what you need.
June 8, 2011 at 1:53 pm
In general, I'm always wary to shrink log files until I'm sure that I've got them under control so I know what size they really ought to be. You don't want to get into a grow, shrink, grow, shrink situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 8, 2011 at 2:00 pm
I appreciate all the feedback.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply