SQL 2005 Rebuild\Reorganize Indexes with Reports

  • Farhan Sabzaali

    Old Hand

    Points: 399

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

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

  • Rome-828069

    Say Hey Kid

    Points: 673

    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.

  • Farhan Sabzaali

    Old Hand

    Points: 399

    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

  • Rome-828069

    Say Hey Kid

    Points: 673

    That worked great. Very nice job.

  • icata

    Hall of Fame

    Points: 3176

    Nice, very comprehensive, lot of work.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720447

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

  • Paul-243651

    SSC Rookie

    Points: 43

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

  • Farhan Sabzaali

    Old Hand

    Points: 399

    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

  • jeff a

    SSC Journeyman

    Points: 86

    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.

  • Farhan Sabzaali

    Old Hand

    Points: 399

    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

  • jeff a

    SSC Journeyman

    Points: 86

    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.

  • FreeHansje

    SSChampion

    Points: 11751

    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

  • willowbloom

    SSC Enthusiast

    Points: 126

    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.

  • Mark Strait

    Grasshopper

    Points: 13

    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

  • JJ-469859

    SSC Eights!

    Points: 953

    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 19 total)

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