•  
  • Indexes
  • INDEX DEFRAGMENTATION SCRIPT for SQL server 2000

INDEX DEFRAGMENTATION SCRIPT for SQL server 2000

 

INDEX DEFRAGMENTATION SCRIPT SQL 2000

Index Defragmentation is one of the most important DBA tasks. This will significantly improve query performance. When you perform any DML operation (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. If you use predefined maintenance plan it will take much server resource and time. Here is a custom stored procedure.

Use the DBCC SHOWCONTIG statement. It will display the fragmentation information.

 

Here I used 3 conditions:

1. Fragmentation >=30 AND PAGES>1000 then rebuild%

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

Share this
21 Comments
  1. Anonymous says:

    Thanks muthu.

    ram

  2. Muthukkumaran Kaliyamoorthy says:

    hmmm.My dear

    I have to tell thanks to u ram because, i find issue while writing the script and learned that too.

  3. Anonymous says:

    I searched in many sql server websites and google but the most of the sites have 2005 & 2008 defragmentation script.

    Finally i found your link in sql server central Thanks for your SQL server 2000 index defragmentation script.Its helped me.

  4. thamal says:

    The site SQL Reports has a great SQL tutorial. Highly recommended for people just getting started on SQL selects.
    http://www.sql-reports.net/

  5. Muthukkumaran Kaliyamoorthy says:

    @Anonymous,
    Glad it helped you.

  6. Muthukkumaran says:

    This is beta version.Please let me know if you faced any issue/error.

    muthukumark1986@gamil.com

  7. Anonymous says:

    How do I run this for all DB ?

  8. Muthukkumaran says:

    Make another one loop or write another one procedure and run the procedure inside this.

    Post me your mail id I'll help you

    • GT says:

      Thank you for your good work.
      Can you please send me the modified script I can use on all the databases on an instance?

      Thanks in advance!
      GT

  9. Lester Grasmick says:

    thanks

  10. LONGOR says:

    Thankyou for sharing INDEX DEFRAGMENTATION SCRIPT for SQL server 2000 Sql server Blog Forum with us keep update bro love your article about INDEX DEFRAGMENTATION SCRIPT for SQL server 2000 Sql server Blog Forum .

  11. Kadin Bilodeau says:

    Awesome blog.Really thank you! Awesome.

  12. rii says:

    Hi tried using this script and i’m getting the below error from one of my database.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    Server: Msg 1038, Level 15, State 3, Line 1
    Cannot use empty object or column names. Use a single space if necessary.
    Server: Msg 1038, Level 15, State 1, Line 1
    Cannot use empty object or column names. Use a single space if necessary.

    If i ran a dbcc reindex manually on that database it does not return any errors.
    Hope you can help me with this.

    Thanks

  13. Terry says:

    I cannot work out how to use this script…
    I pick up that the first part creates tables, and the second part seemingly creates a procedure… but then what?
    1. Am I then supposed to run the procedure?
    If so, what is the syntax for this?
    2. Am I supposed to change anything to point to a specific database?
    If so where do I make this change
    If not does this automatically run against all databases?
    3. The notes suggest to remove the Index Fragmentation to improve…
    Where exactly do you do this?


Welcome , today is Friday, August 1, 2014