Script to check the progress of rebuild Index?

Question

Script to check the progress of rebuild Index?

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
September 19, 2013 at 3:50 am

#283429

Hi,
Is there any good script available to check the progress of Rebuild Index?
Would be helpful.
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

The only thing I can think of is to use the percent_complete column of sys.dm_exec_requests. You'll want to test to find out how accurate and reliable it is.

John

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 2

Try this:

USE MASTER

--

SELECT SESSION_ID, '[' + CAST(DATABASE_ID AS VARCHAR(10)) + '] ' + DB_NAME(DATABASE_ID) AS [DATABASE],

PERCENT_COMPLETE, START_TIME, STATUS, COMMAND,

DATEADD(MS, ESTIMATED_COMPLETION_TIME, GETDATE()) AS ESTIMATED_COMPLETION_TIME, CPU_TIME

FROM SYS.DM_EXEC_REQUESTS

--Apply this Where Clause Filter if you need to check specific events such as Backups, Restores, Index et al.

WHERE COMMAND LIKE '%BACKUP%' OR COMMAND LIKE '%RESTORE%' OR COMMAND LIKE '%INDEX%' OR COMMAND LIKE '%DBCC%'

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

This script doesn't always gives the progress details.

Thanks.

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 4

Here is what I use:

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 Like 'DBCC%'

Or r.command In ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG');

Note: probably will not work on index rebuilds or reorganize operations. This will work for backups, restores and integrity checks.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 5

Jeffrey Williams 3188 (9/23/2013)
Here is what I use:
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 Like 'DBCC%'
Or r.command In ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG');
Note: probably will not work on index rebuilds or reorganize operations. This will work for backups, restores and integrity checks.

That is a great script. Thanks!