SQL 2005 Rebuild\Reorganize Indexes with Reports

Question

Post reply

SQL 2005 Rebuild\Reorganize Indexes with Reports

Farhan Sabzaali

Old Hand

Points: 399
More actions
February 18, 2009 at 3:45 am

#383960

Comments posted to this topic are about the item SQL 2005 Rebuild\Reorganize Indexes with Reports
Farhan F. Sabzaali
PMP, MCP, MCDBA, MCSA, MCSE

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

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

Rome-828069 Say Hey Kid Points: 673 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

Rome-828069

Say Hey Kid

Points: 673

March 16, 2009 at 7:21 am

#960192

That worked great. Very nice job.

icata Hall of Fame Points: 3176 More actions · Answer 4

icata

Hall of Fame

Points: 3176

March 16, 2009 at 8:03 am

#960242

Nice, very comprehensive, lot of work.

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 5

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

Paul-243651 SSC Rookie Points: 43 More actions · Answer 6

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

Farhan Sabzaali Old Hand Points: 399 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 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 More actions · Answer 14

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.