Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Unanswered: Need script generator for DBCC Indexdefrag

    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?

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Just happen to have one already created here. It's a BAT file, so rename the extension to either .BAT or .CMD
    Attached Files Attached Files
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If the number is huge, you'll get old before you get done. Is that what you really want?

    -PatP

  4. #4
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Well

    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

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Well

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •