25112
asked on
rebuild process hangs..
this code is automated to run everyday.. but only last 2 days it is hanging and thus locking other processes also.. could this code be made more efficient to avoid locks or graciously quit without locking other process, in a failure or delay?
(I believe this is public code from online, perhaps Microsoft)
~~~
USE ROSER
GO
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_perce nt AS frag
INTO #CollectFragInfo
FROM sys.dm_db_index_physical_s tats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_perce nt > 10.0 AND index_id > 0 and page_count>99;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #CollectFragInfo;
-- Open the cursor.
OPEN partitions;
-- Loop through the Indexes to be defragged.
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
SELECT @objectname = o.name, @schemaname = 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 = 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 >= 10.0
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;
CLOSE partitions;
DEALLOCATE partitions;
(I believe this is public code from online, perhaps Microsoft)
~~~
USE ROSER
GO
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_perce
INTO #CollectFragInfo
FROM sys.dm_db_index_physical_s
WHERE avg_fragmentation_in_perce
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #CollectFragInfo;
-- Open the cursor.
OPEN partitions;
-- Loop through the Indexes to be defragged.
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
SELECT @objectname = o.name, @schemaname = 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 = 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 >= 10.0
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;
CLOSE partitions;
DEALLOCATE partitions;
ASKER
thank you.
now, this job had never failed for 6 weeks, and suddenly started this way for now 3 days in a row. what can cause this kind of locking to happen all of a sudden?
now, this job had never failed for 6 weeks, and suddenly started this way for now 3 days in a row. what can cause this kind of locking to happen all of a sudden?
There's some good reasons for that happens and I'll bet in one of the following:
- Database growth
- Another processes running in concurrence
- Database growth
- Another processes running in concurrence
ASKER
Vitor, I tried your 2 reco, but this morning it again failed.. any thoughts what we can try next?
the 2 steps we implemented:
>30
=ONLINE option.
there is no errors captured in error log.
the process behind this has to be manually killed by command to relieve the lock, and until then there is no relief.
the 2 steps we implemented:
>30
=ONLINE option.
there is no errors captured in error log.
the process behind this has to be manually killed by command to relieve the lock, and until then there is no relief.
The process still fails or ends with success?
ASKER
still fails.. it causes lock that affects the application.. manual intervention is then needed to kill the process runs the above code and only then relief.. after 6 weeks, surprising this happens everyday.. pl suggest.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can also rebuild online. It will take longer but will avoid locks. Just add the ONLINE=ON option to the command.
Open in new window