February 7, 2011 at 4:27 pm
I use mentioned below sql to find the status of process like index rebuild, restore job etc. I got this script from one of the training sessions. However sometime the script does not give the correct data. Does anyone have a better script?
SELECT r.percent_complete,
estimated_finish_time = DATEADD(MILLISECOND, estimated_completion_time, CURRENT_TIMESTAMP),
t.[text]FROM sys.dm_exec_requests AS r CROSS APPLY
sys.dm_exec_sql_text(r.[sql_handle]) AS t WHERE r.session_id = 59
February 7, 2011 at 4:51 pm
I generally use
select percent_complete, estimated_completion_time/60000.0 'EstimatedMinutesRemaining'
, *
from sys.dm_exec_requests
where command like 'backup%'
However, the Estimated_Completion_Time is not an accurate figure. It does give me a close enough figure on how long it would take to finish.
In BOL, it is marked as Internal Use Only.
You can use Stats option in the Backup / Restore commands. They would give the same information as is displayed by the about DMV.
February 8, 2011 at 6:27 am
select es.session_id,es.status,wt.exec_context_id,wt.wait_type,wt.blocking_session_id,wt.blocking_exec_context_id, wt.resource_description, wt.wait_duration_ms
from sys.dm_exec_sessions es
inner join sys.dm_os_waiting_tasks wt on es.session_id = wt.session_id
where es.is_user_process = 1
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 9, 2011 at 10:22 pm
SKYBVI (2/8/2011)
select es.session_id,es.status,wt.exec_context_id,wt.wait_type,wt.blocking_session_id,wt.blocking_exec_context_id, wt.resource_description, wt.wait_duration_msfrom sys.dm_exec_sessions es
inner join sys.dm_os_waiting_tasks wt on es.session_id = wt.session_id
where es.is_user_process = 1
Regards,
Sushant
Thank Sushant. This doesn't give what i need.I need to findout how much more time(estimate time) will it take to complete a process like restore and rebuild index.
February 9, 2011 at 10:25 pm
pankushmehta (2/7/2011)
I generally useselect percent_complete, estimated_completion_time/60000.0 'EstimatedMinutesRemaining'
, *
from sys.dm_exec_requests
where command like 'backup%'
However, the Estimated_Completion_Time is not an accurate figure. It does give me a close enough figure on how long it would take to finish.
In BOL, it is marked as Internal Use Only.
You can use Stats option in the Backup / Restore commands. They would give the same information as is displayed by the about DMV.
I am performing a restore now and shows '0' secs from past two minutes. This neither works for rebuilding indexes. Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply