| Author |
Topic |
|
JaybeeSQL
Posting Yak Master
112 Posts |
 Posted - 2012-06-28 : 09:13:18
|
| Hi all,Got a few Db's to defragment and wanted to check my plan with you all....1) Take full backups;2) Switch recovery model from Bulk to Simple, OR increase log shipping frequency from 15 to 2 mins;3) Run Defrag script (for all indices on all db's);4) Switch back to bulk-logged or 15 mins shipping;5) Run ShowContig (for all indices on all db's).How's this look??Cheers,JB |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2012-06-28 : 09:36:00
|
| I would leave the recovery model alone.To prevent log file expansion or overload, I use a script that does a full update statistics on a table just after it is defraged, and then does a wait for delay 30 seconds in a loop as long at the transaction log is more than 50% full. Usually, the update statistics takes long enough to run that there is no delay waiting for the log backups to clear the log. You should update the statistics anyway after a defrag, so it really isn't any additional processing.This makes it unnecessary to do any of the things that you mentioned by introducing times where there no transaction log activity to allow the transaction log backups to keep up.CODO ERGO SUM |
 |
|
|
JaybeeSQL
Posting Yak Master
112 Posts |
Posted - 2012-06-28 : 10:30:40
|
| You have a link to this script? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2012-06-28 : 11:58:51
|
quote:
Originally posted by JaybeeSQL
You have a link to this script?
Sorry, I wrote it myself and have never posted it online.CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-06-28 : 12:43:59
|
| I would never change the recovery model as that eliminates point in time recovery for that time period. That would be unacceptable to my customers and our business. Plus you can't switch recovery model to SIMPLE if you are using log shipping as that breaks the tlog chain and therefore will break log shipping.I'd instead suggest increasing the tlog backup frequency to perhaps 1-2 minutes. We just keep ours at 15 minutes though. Increasing the tlog backup frequency only helps for smallish indexes. If you've got a large index that takes more minutes than the frequency of the tlog backup, then it won't matter as it can't clear it until after that completes and until the next tlog backup runs.Why are you running ShowContig? We're currently going down the road of disabling our rebuild/reorg (defrag) jobs as it causes too much pain for no or very, very little gain. I'd instead suggest doing performance testing to see if you even need it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2012-06-28 : 15:20:44
|
quote:
Originally posted by tkizer
I would never change the recovery model as that eliminates point in time recovery for that time period. That would be unacceptable to my customers and our business. Plus you can't switch recovery model to SIMPLE if you are using log shipping as that breaks the tlog chain and therefore will break log shipping.I'd instead suggest increasing the tlog backup frequency to perhaps 1-2 minutes. We just keep ours at 15 minutes though. Increasing the tlog backup frequency only helps for smallish indexes. If you've got a large index that takes more minutes than the frequency of the tlog backup, then it won't matter as it can't clear it until after that completes and until the next tlog backup runs.Why are you running ShowContig? We're currently going down the road of disabling our rebuild/reorg (defrag) jobs as it causes too much pain for no or very, very little gain. I'd instead suggest doing performance testing to see if you even need it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
If you are doing index rebuilds, everthing for an index is in one transaction so it is harder to control the log file usage.If you are doing index defragmentation, the data is committed in smaller batches, and it is easier to control log growth.CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
JaybeeSQL
Posting Yak Master
112 Posts |
Posted - 2012-06-28 : 18:37:06
|
Hi Tara,quote:
Originally posted by tkizer
I would never change the recovery model as that eliminates point in time recovery for that time period. That would be unacceptable to my customers and our business. Plus you can't switch recovery model to SIMPLE if you are using log shipping as that breaks the tlog chain and therefore will break log shipping.
You sure it will? I am told my predecessor switched back and forth, though to be fair I have not YET seen this in the scant docu I have gotten. quote:
I'd instead suggest increasing the tlog backup frequency to perhaps 1-2 minutes. We just keep ours at 15 minutes though. Increasing the tlog backup frequency only helps for smallish indexes. If you've got a large index that takes more minutes than the frequency of the tlog backup, then it won't matter as it can't clear it until after that completes and until the next tlog backup runs.
????? Lots of 'it's and a 'that' - what won't matter as what can't clear what until after what completes? quote:
Why are you running ShowContig?
So we have a before/after picture. We had basically disabled regular defrags but haven't done them for at least 3 months, since I've been there.I am indeed talking about defrags (reorgs, NOT rebuilds). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-06-29 : 13:05:46
|
quote:
Originally posted by JaybeeSQL
Hi Tara,quote:
Originally posted by tkizer
I would never change the recovery model as that eliminates point in time recovery for that time period. That would be unacceptable to my customers and our business. Plus you can't switch recovery model to SIMPLE if you are using log shipping as that breaks the tlog chain and therefore will break log shipping.
You sure it will? I am told my predecessor switched back and forth, though to be fair I have not YET seen this in the scant docu I have gotten.
Yes I'm positive.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
IndexDefrag