Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2005 Rebuild\Reorganize Indexes with Reports


SQL 2005 Rebuild\Reorganize Indexes with Reports

Author
Message
jeff a
jeff a
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
FreeHansje
FreeHansje
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
Points: 885 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
willowbloom
willowbloom
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
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.
Mark Strait
Mark Strait
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 87
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
JJ-469859
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 829
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.
SA-1
SA-1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 944
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.
Farhan Sabzaali
Farhan Sabzaali
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 152
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
markryan003
markryan003
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
adb2303
adb2303
SSC Eights!
SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)

Group: General Forum Members
Points: 962 Visits: 2886
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search