SQL 2005 Rebuild\Reorganize Indexes with Reports

Question

SQL 2005 Rebuild\Reorganize Indexes with Reports

Viewing 4 posts - 16 through 18 (of 18 total)

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

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 1

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 Old Hand Points: 399 More actions · Answer 2

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 Newbie Points: 5 More actions · Answer 3

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 SSCertifiable Points: 7588 More actions · Answer 4

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