SQL 2005 Rebuild\Reorganize Indexes with Reports

  • Comments posted to this topic are about the item SQL 2005 Rebuild\Reorganize Indexes with Reports

    Farhan F. Sabzaali
    PMP, MCP, MCDBA, MCSA, MCSE

  • Maybe I am missing a step but this proc doesn't compile.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 57

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 146

    ....

    edited out numerous errors.

  • It seems the script that got published has space unicode characters in it - I am attaching a zip file with compiled query - let me know if that works for you.

    Thanks,

    Farhan Sabzaali

    Farhan F. Sabzaali
    PMP, MCP, MCDBA, MCSA, MCSE

  • That worked great. Very nice job.

  • Nice, very comprehensive, lot of work.

  • I have updated the script with code from Farhan as well.

  • The comments in the sp say its for SQL 2005 only, but will it run on sql 2008?

  • To be honest, I have not tried as I don't have access to one - if you can test it on dev or test environment, it would help.

    Farhan F. Sabzaali
    PMP, MCP, MCDBA, MCSA, MCSE

  • This has been working well for me for several weeks, then we recently updated a vendor product and I'm getting an error. Looks like they updated their code to frequently create temporary tables and delete them. It appears the rebuild procedure doesn't handle tables that have been deleted after the procedure was started:

    Msg 1088, Level 16, State 9, Line 1

    Cannot find the object "NetPerfMon.dbo.NetFlowDetail_579_1382041" because it does not exist or you do not have permissions.

    Any suggestions appreciated.

  • Ideally, you should run these type of jobs when database is in maintenance window - if possible, try to schedule it when the application is not using the DB. If that is not possible, try running it with Online option = 0 - that should only look for existing tables. If that does not work, I may have to review code in detail to help you.

    Farhan F. Sabzaali
    PMP, MCP, MCDBA, MCSA, MCSE

  • Unfortunately, this database is always online and fairly busy. It captures our netflow statistics from all our networking hardware.

    I'm thinking we just need to put a check into the fetch loop to see if the table/index was deleted after the initial table/index query was done. (similar to the check for the index being disabled).

    -- Loop through the tables, indexes and partitions.

    FETCH NEXT

    FROM Local_Rebuildindex_Cursor

    INTO @indexid, @tableid, @indexname, @objectname, @schemaname, @indextype, @partitionnum, @partitioncount, @currentdensity, @currentfrag;

    WHILE @@FETCH_STATUS = 0

    BEGIN;

    SET @StatusMsg = @StatusMsg + @NewLine

    -- ***** Check if index/table is still available, if not then do not process it, print message.

  • We use a similar script and recently partitiond a database. Now I receive a syntaxerror on this statement:

    ALTER INDEX [MUTA_PI3]

    ON [prd_00rpd_hrm].[hrm].[MUTA]

    REBUILD

    WITH

    (ONLINE = ON,

    FILLFACTOR = 90,

    MAXDOP = 0)

    PARTITION = 2;

    error: Msg 102, Level 15, State 1, Line 8

    Incorrect syntax near 'PARTITION'.

    I can't figure out what the correct syntax is. Leaving out the PARTITION statement works, leaving out the WITH statement works. I can't find an example combining the 2.

    Help please?

    Greetz,
    Hans Brouwer

  • ALTER INDEX [MUTA_PI3]

    ON [prd_00rpd_hrm].[hrm].[MUTA]

    REBUILD

    WITH

    (ONLINE = ON,

    FILLFACTOR = 90,

    MAXDOP = 0)

    PARTITION = 2;

    From http://technet.microsoft.com/en-us/library/ms188388.aspx, it seems you need to move the PARTITION = 2 before the index options.

  • The script is awesome. We know that a rebuild updates the statistics. But do we need to run an update of the statistics for the indexes that get reorganized from this code? Since the script is intelligent and only does the indexes that need it I would assume we wouldn't want to do a full statistics update on everything as well. Has this thought come up and I missed it?

    Thanks,

    Mark

  • In SQL 2k5 (not 2000) you should run sp_updatestats after this job and it will update only statistics that are needed.

    It doesn't look like the @activeconnectionsindb variable is populated in this stored procedure. For example, I have an instance running Enterprise edition and set online= 0.... This script makes it seem like it should try and rebuild online anyways but since @activeconnectionsindb is not populated, it just rebuilds them as normal offline.

Viewing 15 posts - 1 through 15 (of 18 total)

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