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


Rebuild/Reorganize Indexes


Rebuild/Reorganize Indexes

Author
Message
Ed-586976
Ed-586976
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 406
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')
   WHERE   avg_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.
Ed-586976
Ed-586976
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 406
Nevermind, I added the database name in front of the schema name to solve the problem.
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21029 Visits: 18258
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

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