SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Farhan Sabzaali / SQL 2005 Rebuild\Reorganize Indexes with Reports / Latest Posts

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

adb2303 — Wed, 27 Jul 2011 03:39:59 GMT

Hi,This script is great - thanks. One small problem I've found, if the database collation uses a binary sort, I get an error stating that <databasename>.dbo.sys.Partitions could not be found, due to its case sensitivity. It requires a simple change to sys.partitions to fix this.Thanks,Andrew

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

markryan003 — Mon, 02 Nov 2009 16:44:38 GMT

Hi Guys, I hope my script can helpUSE DATABASENAME;SET NOCOUNT ON;DECLARE @objectid int;DECLARE @indexid int;DECLARE @partitioncount bigint;DECLARE @schemaname nvarchar(130);DECLARE @objectname nvarchar(130);DECLARE @indexname nvarchar(130);DECLARE @partitionnum bigint;DECLARE @partitions bigint;DECLARE @frag float;DECLARE @command nvarchar(4000);DECLARE @dbid smallint;SET @dbid = DB_ID();SELECT [object_id] AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag, page_countINTO #work_to_doFROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')WHERE avg_fragmentation_in_percent > 5.0 -- Allow limited fragmentation AND index_id > 0 -- Ignore heaps AND page_count > 10; -- Ignore small tablesDECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;OPEN partitions;WHILE (1=1) BEGIN FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); EXEC (@command); PRINT N'Executed: ' + @command; ENDCLOSE partitions;DEALLOCATE partitions;DROP TABLE #work_to_do;GO

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

Farhan Sabzaali — Thu, 03 Sep 2009 14:22:32 GMT

I am sorry everyone but I have moved to France working in a NGO so I have very little time these days to work on \ look at SQL - you are free to adapt \ modify this script with one request to share the updated one with the community so all can benefit from it.Thanks,Farhan

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

SA-1 — Thu, 03 Sep 2009 08:41:42 GMT

Has there been any thought about excluding snapshot databases as well from the process. I tried this with a snapshot database on a server and it stopped processing.

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

JJ-469859 — Wed, 02 Sep 2009 13:12:23 GMT

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.

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

straitm-986199 — Thu, 23 Jul 2009 13:56:22 GMT

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

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

willowbloom — Tue, 21 Jul 2009 22:59:27 GMT

[quote][i]ALTER INDEX [MUTA_PI3] ON [prd_00rpd_hrm].[hrm].[MUTA] REBUILD WITH (ONLINE = ON, FILLFACTOR = 90, MAXDOP = 0) PARTITION = 2;[/i][/quote]From http://technet.microsoft.com/en-us/library/ms188388.aspx, it seems you need to move the PARTITION = 2 before the index options.

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

FreeHansje — Fri, 26 Jun 2009 02:20:13 GMT

We use a similar script and recently partitiond a database. Now I receive a syntaxerror on this statement:[i]ALTER INDEX [MUTA_PI3] ON [prd_00rpd_hrm].[hrm].[MUTA] REBUILD WITH (ONLINE = ON, FILLFACTOR = 90, MAXDOP = 0) PARTITION = 2;[/i][i]error: Msg 102, Level 15, State 1, Line 8Incorrect syntax near 'PARTITION'.[/i]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?

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

jeff a — Fri, 12 Jun 2009 07:44:16 GMT

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 [b]-- ***** Check if index/table is still available, if not then do not process it, print message.[/b]

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

Farhan Sabzaali — Mon, 01 Jun 2009 12:17:40 GMT

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.

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

jeff a — Mon, 01 Jun 2009 12:11:10 GMT

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 1Cannot find the object "NetPerfMon.dbo.NetFlowDetail_579_1382041" because it does not exist or you do not have permissions.Any suggestions appreciated.

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

Farhan Sabzaali — Mon, 16 Mar 2009 20:35:54 GMT

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.

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

Paul-243651 — Mon, 16 Mar 2009 18:19:23 GMT

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

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

Steve Jones - SSC Editor — Mon, 16 Mar 2009 09:11:11 GMT

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

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

icata — Mon, 16 Mar 2009 08:03:43 GMT

Nice, very comprehensive, lot of work.

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

Rome-828069 — Mon, 16 Mar 2009 07:21:41 GMT

That worked great. Very nice job.

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

Farhan Sabzaali — Mon, 16 Mar 2009 06:54:40 GMT

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

RE: SQL 2005 Rebuild\Reorganize Indexes with Reports

Rome-828069 — Mon, 16 Mar 2009 06:11:33 GMT

Maybe I am missing a step but this proc doesn't compile.Msg 102, Level 15, State 1, Line 3Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 57Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 146....edited out numerous errors.

SQL 2005 Rebuild\Reorganize Indexes with Reports

Farhan Sabzaali — Wed, 18 Feb 2009 03:45:13 GMT

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