how to kill process of SQL Server (v 17.5) Index rebuild

Question

I rebuild a index (Using GUI Menu on SSMS) and it's showing status as error since yesterday, but progress showing executing. While I tried see any process going on with Alter index, it's showing nothing with the following script. Can anyone help me to kill this process or if there is any blockage to resume this process, I want to move forward from here. This process is sucking my Drive space.

Select r.command  
 , s.text  
 , r.start_time  
 , r.percent_complete  
 , cast(((datediff(second, r.start_time, getdate())) / 3600) As varchar) + ' hour(s), '  
 + cast((datediff(second, r.start_time, getdate()) % 3600) / 60 As varchar) + 'min, '  
 + cast((datediff(second, r.start_time, getdate()) % 60) As varchar) + ' sec' As running_time  
 , cast((r.estimated_completion_time / 3600000) As varchar) + ' hour(s), '  
 + cast((r.estimated_completion_time % 3600000) / 60000 As varchar) + 'min, '  
 + cast((r.estimated_completion_time % 60000) / 1000 As varchar) + ' sec' As est_time_to_go  
 , dateadd(second, r.estimated_completion_time / 1000, getdate()) As est_completion_time  
    From sys.dm_exec_requests     r                  
   Cross Apply sys.dm_exec_sql_text(r.sql_handle) s  
   Where r.command IN ('Alter Index')  
   ----   Or r.command Like 'DBCC%'  
   ----   Or r.command In ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG');  

Answer 1

Using GUI Menu on SSMS

Better never use SSMS GUID/Wizards, they are not very reliable, use always plain T-SQL in a query windows. You could use GUI to generate SQL scrips for the commands in mind.

but progress showing executing

Shown where; in the GUI windows? See my previous comment.

While I tried see any process going on with Alter index, it's showing nothing

Use instead:

select *  
from sys.sysprocesses as pr  
where pr.cmd = N'DBCC';  

Index rebuild

It's possible to kill an INDEX REBUILD process, but then may all indexes gets corrupt and you have to rebuild them again; so you don't win anything on stop the process.

Answer 2

Hi

Thanks for reaching out to Microsoft Q&A.

Never use GUI in ssms other than for basic administration and script generation.

As olafHelper has mentioned killing an index job is a worst idea. The index job takes so much time because it internally creates a new index first and then drops the old one, hence you are seeing your disk's free space going down fast. For such operations killing will not bring everything back to normal, it just gets held in 'Rolledback' loop until it completely rolls back everything which in-turn is going to take much time as you would wait for your previous index rebuild job itself to complete.

Please Upvote and Accept as answer if the reply was helpful, this will be helpful to other community members.

Answer 3

One challenge with answering questions in these forums is that we far from always know what sort of environment the poster is asking about. It's a big difference between a production environment and your local instance of SQL Server on your laptop.

I'm reading between the lines and assume the latter, but I'm not sure. Please correct or clarify.

I would guess that the reason that ALTER INDEX is stalled is because it is blocked, for instance by a transaction that has been left open. And then everyone else who want to access that table gets blocked by ALTER INDEX.

You can use my beta_lockinfo to get information about blocking and current activity.

Answer 4

Hi @Muhammad Azhar Shahzad ,

Please refer to this doc:

Perform Index Operations Online

While an index is being rebuilt, this may result in a minor performance degradation of data modification statements during online rebuild

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.