Post reply

DB Reindex script needed

  • May 11, 2006 at 4:24 am

    #152964

    Hi all,

    Does anyone have a good DB Reindex script I can use? Here's the one I've got from another website, but it keeps falling over, looking for tables that aren't there and then (presumably) the whole job gets rolled back:

    CREATE PROC ind_rebuild

    AS

    DECLARE @TableName sysname

    DECLARE cur_reindex CURSOR FOR

    SELECT table_name

    FROM information_schema.tables

    WHERE table_type = 'base table'

    OPEN cur_reindex

    FETCH NEXT FROM cur_reindex INTO @TableName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT 'Reindexing ' + @TableName + ' table'

    DBCC DBREINDEX (@TableName, ' ', 80)

    FETCH NEXT FROM cur_reindex INTO @TableName

    END

    CLOSE cur_reindex

    DEALLOCATE cur_reindex

    GO

    On a sidenote, doesn't CREATE PROC create another instance of 'Ind_rebuild' every time it's run??

    Thanks,

    Jaybee.

  • May 11, 2006 at 4:28 am

    #637185

    This should do it

    sp_MSforeachtable

    @command1="print '?' dbcc DBREINDEX ('?')"

    so_MSForEachTable is undocumnented stored procedure

     

    hth

     

    David

  • May 11, 2006 at 8:54 am

    #637283

    I don't understand the syntax too well, where do I change the database name or how do I pass the name into the Exec statement? And can I change the fill-factors, or are they just rebuilt as they originally were?

    Thanks,

    Jaybee.

  • May 11, 2006 at 11:54 am

    #637358

    It just enumrates each table in the database so to use it in a particular database you would just do this

     

    USE Database

    GO

    sp_MSforeachtable @command1 = "print '?' dbcc DBREINDEX ('?')"

     

    This will only rebuild it with the original fill factors.

  • May 11, 2006 at 2:10 pm

    #637392

    USE Northwind (for example)

    GO

    sp_MSforeachtable @command1 = "print '?' dbcc DBREINDEX ('?')"

     

    That's it?!?!???  I can put that in a scheduled job and have it reindex my db???

    Jaybee.

     

  • May 11, 2006 at 3:24 pm

    #637406

    Yeh thats it.

     

  • May 12, 2006 at 8:38 am

    #637585

    If you're doing all the tables in the database, your can use a maintenance plan to perform it as well. Create the plan with the wizard, schedule it as frquently as you need it done and, voila, it's done!


    Terry

  • May 14, 2006 at 5:55 am

    #637739

    You know, that's just how I ended up doing it...all the other scripts kept falling over due to missing tables, but I didn't even realise the Maint Plan had a defragger...!!!  Just goes to show how rusty I am.

    Thanks all, but special thanks to Mr Crosby!!

     

    Jaybee.

  • May 17, 2006 at 8:51 am

    #638442

    Any thoughts/ideas as to a good way to defrag/reindex a db that is in 6.5 compatibility and creates TONS of blocking locks?  Using the maintenance wizard kills the db...we're online 365x24x7, so there really isn't a good time to do maintenance. 

Viewing 9 posts - 1 through 8 (of 8 total)

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