SQL 2005 Rebuild\Reorganize Indexes with Reports

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Farhan Sabzaali » SQL 2005 Rebuild\Reorganize Indexes with...

19 posts, Page 2 of 2

««1 2

SQL 2005 Rebuild\Reorganize Indexes with Reports

Rate Topic

Display Mode

Topic Options

Author

Message

jeff a

Posted Friday, June 12, 2009 7:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 26, 2009 6:25 AM
Points: 2, Visits: 9

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.

Post #733835

FreeHansje

Posted Friday, June 26, 2009 2:20 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 06, 2012 8:30 AM
Points: 879, Visits: 810

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

Post #742455

willowbloom

Posted Tuesday, July 21, 2009 10:59 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 22, 2010 1:52 PM
Points: 122, Visits: 45

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.

Post #757148

straitm-986199

Posted Thursday, July 23, 2009 1:56 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 6:22 AM
Points: 7, Visits: 53

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

Post #758571

JJ-469859

Posted Wednesday, September 02, 2009 1:12 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, April 08, 2013 8:51 AM
Points: 14, Visits: 653

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.

Post #781756

SA-1

Posted Thursday, September 03, 2009 8:41 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 7:35 AM
Points: 1,140, Visits: 933

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.

Post #782241

Farhan Sabzaali

Posted Thursday, September 03, 2009 2:22 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, December 16, 2011 2:21 AM
Points: 76, Visits: 135

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

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

Post #782555

markryan003

Posted Monday, November 02, 2009 4:44 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, October 17, 2011 11:02 AM
Points: 1, Visits: 29

Hi Guys,

I hope my script can help

USE 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_count

INTO #work_to_do

FROM 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 tables

DECLARE 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;

END

CLOSE partitions;

DEALLOCATE partitions;

DROP TABLE #work_to_do;

GO

Post #812655

adb2303

Posted Wednesday, July 27, 2011 3:39 AM

SSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:02 AM
Points: 655, Visits: 1,860

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

Post #1148925

« Prev Topic | Next Topic »

19 posts, Page 2 of 2

««1 2

Permissions