Rebuild/Reorganize Indexes

  • The following rebuild index scripts which was fine for a long time now has suddenly stopped rebuilding indexes when running from master database. But if the script is running on the same database where we are rebuilding the indexes it works perfect. Can someone please let me know what is wrong with the script that is preventing it to run from the master database and rebuild the indexes??

    Here is the script.

    USE [master]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE procedure [dbo].[usp_alter_index](@dbname varchar(100), @reorg_percent float=10.0, @rebuild_percent float=30.0)

    as

    begin

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

    -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

    -- and convert object and index IDs to names.

    SELECT

    object_id AS objectid,

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS frag

    INTO #work_to_do

    FROM sys.dm_db_index_physical_stats (DB_ID(@dbname), NULL, NULL , NULL, 'LIMITED')

    WHEREavg_fragmentation_in_percent > 10.0 -- Select fragmentation greater than 10

    AND index_id > 0 -- Ignore heaps

    AND page_count > 25; -- Ignore small tables

    -- Declare the cursor for the list of partitions to be processed.

    DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

    -- Open the cursor.

    OPEN partitions;

    -- Loop through the 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;

    /* Fragmentation greater than 30% should be rebuilt and fragmentation less than 30% should be reorganized.*/

    IF @frag < @rebuild_percent

    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

    IF @frag >= @rebuild_percent

    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (ONLINE=ON)';

    IF @partitioncount > 1

    SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

    EXEC (@command);

    PRINT N'Executed: ' + @command;

    END;

    -- Close and deallocate the cursor.

    CLOSE partitions;

    DEALLOCATE partitions;

    select * from #work_to_do

    -- Drop the temporary table.

    DROP TABLE #work_to_do;

    end

    GO

    Thanks in advance.

  • Nevermind, I added the database name in front of the schema name to solve the problem.

  • Thanks for updating us with your fix.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 3 posts - 1 through 2 (of 2 total)

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