September 19, 2013 at 3:50 am
Hi,
Is there any good script available to check the progress of Rebuild Index?
Would be helpful.
Thanks.
September 19, 2013 at 4:03 am
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
September 23, 2013 at 7:23 am
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%'
September 23, 2013 at 10:07 am
This script doesn't always gives the progress details.
Thanks.
September 23, 2013 at 1:01 pm
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
September 23, 2013 at 1:07 pm
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!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy