Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SQL 2005 Rebuild\Reorganize Indexes with Reports Expand / Collapse
Author
Message
Posted Friday, June 12, 2009 7:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, June 26, 2009 2:20 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 6, 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
Posted Tuesday, July 21, 2009 10:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Thursday, July 23, 2009 1:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 7, 2014 11:33 AM
Points: 7, Visits: 85
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
Posted Wednesday, September 2, 2009 1:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 15, Visits: 709
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
Posted Thursday, September 3, 2009 8:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 2:58 PM
Points: 1,141, 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.
Post #782241
Posted Thursday, September 3, 2009 2:22 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 2:31 PM
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
Post #782555
Posted Monday, November 2, 2009 4:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, July 27, 2011 3:39 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 6:49 AM
Points: 811, Visits: 2,418
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 »

Add to briefcase ««12

Permissions Expand / Collapse