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)
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);
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #CollectFragInfo
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 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.
   FROM partitions
   INTO @objectid, @indexid, @partitionnum, @frag;
        SELECT @objectname =, @schemaname =
        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
    SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD';
    IF @partitioncount > 1
        SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
    EXEC (@command);
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;

CLOSE partitions;
DEALLOCATE partitions;
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
It's rebuilding indexes when fragmentation it's higher than 10% (IF @frag >= 10.0). Recommendation it's to rebuild only when fragmentation it's higher than 30%. Check the MSDN article about this.
You can also rebuild online. It will take longer but will avoid locks. Just add the ONLINE=ON option to the command.
IF @frag >= 30.0
     SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD 
     IF @partitioncount > 1
         SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
     EXEC (@command);

Open in new window

25112Author Commented:
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?
Vitor MontalvãoMSSQL Senior EngineerCommented:
There's some good reasons for that happens and I'll bet in one of the following:
- Database growth
- Another processes running in concurrence
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

25112Author Commented:
Vitor, I tried your 2 reco, but this morning it again failed.. any thoughts what we can try next?

the 2 steps we implemented:
=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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
The process still fails or ends with success?
25112Author Commented:
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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Depending on the fragmentation level you won't need to rebuild indexes every day. In 90% of the cases once by week it's enough, so I would recommend you to execute that job weekly on a day and hour where no one is working. Usually Sundays evening so it can take all night long if necessary.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.