Post reply

Single command to rebuild all the indexes of all the tables

  • November 26, 2010 at 10:32 pm

    #231605

    Hi Experts,

    In sql server 2005, Is there any single command to rebuild ALL the indexes of ALL the TABLES in a particular database in one shot..?

    and

    Is there any way to calculate how much it's going to take rebuild all the indexes.??

    Thanks in advance.

  • November 27, 2010 at 1:54 am

    #1255944

    No and No.

    There's a command to rebuild all the indexes on a table. Doing a rebuild on every single index of every single table is, quite honestly, something you don't want to do unless you've got a 50MB database. Rebuild just indexes that need it. There are lots of good tested scripts to do this. Maybe start with Michelle's (www.sqlfool.com)

    As for how long they take, how long did they take last time?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 27, 2010 at 3:06 am

    #1255950

    Hi Gila,

    Thanks for your reply.

    It's a 185 GB database and defragmentation is not done since long time it seems. Heavily fragmented it is. I executing "sp_msforeachtable" proc. it will take around approx 1.30 hours it I believe. Henceforth I will do only for those indexes that's required to be rebuilt.

    Thanks once again.

  • November 27, 2010 at 6:23 am

    #1255966

    Joy Smith San (11/27/2010)

    It's a 185 GB database and defragmentation is not done since long time it seems. Heavily fragmented it is.

    With this size database, as Gail says, I wouldnt blindly rebuild all indexes as this could take a lot longer than 1.3 hours

    Michelle Ufford's script is very good and I recommend it highly, you may also want to check Ola Hallengren's script too!

    Are you currently using something along the lines of this?

    exec sp_msforeachtable @command1 = "ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON);"

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • November 28, 2010 at 11:38 pm

    #1256120

    YES.... I am using the same..?

    Any problem with it..?

    by the way "rbuild" took 2.30 hrs to complete.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply