Friends
I want to run DBCC INDEXDEFRAG(Db_name, Tab, Idx) for many of the databases . Number is huge and it is near impossible to go to each server and do a manual run. Can someone provide me a scrip to generate the above syntex for all the tables in a db?
ThanksJust happen to have one already created here. It's a BAT file, so rename the extension to either .BAT or .CMD|||If the number is huge, you'll get old before you get done. Is that what you really want?
-PatP|||Thanks for the above file . It is not really that huge. However databases are very large . in access of 350+ gb and I am planning on running them on Weekend when there is little load. Let you guys know how it works out.
Thanks|||BTW, if you want to test it against Northwind, you need to modify line #16 where there is the following:
+ ', ' + o.name + ', ' +
Change it to:
+ ', [' + o.name + '], ' +
Otherwise it'll bomb on non-standard names.
Also, if you want to keep your job, you need to be cautious about running it unattended against potentially highly fragmented indexes. You should probably perform analysis of tables and identify fragmentation degree using SHOWCONTIG.|||I'd expect the defrags to run on 350 Gb in under a week for most cases. That might be somewhat sub-optimal, career-wise.
As rdjabarov suggested, I'd probably do some analysis and selectively run one defrag at a time, when I could at least periodically monitor its behavior.
-PatP|||Hey, don't get me wrong, once the initial defrag is complete, and the percent of fragmentation that gets introduced on a daily basis is identified, - use the script, it's safe. But for the initial defragmentation effort I wouldn't rely on automation unless I know that defrag would not take weeks as Pat suggested. BOL mentions that depending on the fragmentation degree, rebuilding the index may or may not be faster than defragmenting it.|||Guys
Thanks for all the concern raised . I am not going to dump it on Prod as yet. This is one of the steps I was inetrested in. I am going to use it on one of our Sort of QA/Test servers and will see how much time it really takes doing it.
And yes. initial run will be all manual larger tables one at a time on weekend.
Thanks for all your help
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment