July 25, 2011 at 5:06 am
I had a import job job running for 8 hrs, asusming that it is taking too long i killed the job and it is more than 8 hrs i killed but still rolling back, is there way to stop it or can i find expected time to finish, I dont see any infoirmation in the sql log.
July 25, 2011 at 5:12 am
SELECT r.[session_id]
, c.[client_net_address]
, s.[host_name]
, c.[connect_time]
, [request_start_time] = s.[last_request_start_time]
, [current_time] = CURRENT_TIMESTAMP
, r.[percent_complete]
, [estimated_finish_time] = DATEADD(MILLISECOND, r.[estimated_completion_time], CURRENT_TIMESTAMP)
, [estimated_seconds_left] = DATEDIFF(s, CURRENT_TIMESTAMP, DATEADD(MILLISECOND, r.[estimated_completion_time], CURRENT_TIMESTAMP))
, current_command = SUBSTRING ( t.[text], r.[statement_start_offset]/2, COALESCE(NULLIF(r.[statement_end_offset], -1)/2, 2147483647) )
, module = COALESCE(QUOTENAME(OBJECT_SCHEMA_NAME(t.[objectid], t.[dbid])) + '.' + QUOTENAME(OBJECT_NAME(t.[objectid], t.[dbid])), '<ad hoc>')
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_connections AS c
ON r.[session_id] = c.[session_id]
INNER JOIN sys.dm_exec_sessions AS s
ON r.[session_id] = s.[session_id]
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE r.[percent_complete] <> 0;
July 25, 2011 at 6:45 am
the percent complete for the sesison i am looking is "0", does it mean it is not rolling back at all?
July 25, 2011 at 6:54 am
Run sp_WhoIsActive to see if the connection is still doing something.
It might be waiting on another ressource to finish.
And no restarting the server won't help. That transaction still has to rollback. But if you restart nothing else will be able to run untill that completes.
July 25, 2011 at 7:20 am
yes, from the actvity monitor (sp_who2) it shows me as rolling back.
July 25, 2011 at 7:38 am
Tara-1044200 (7/25/2011)
yes, from the actvity monitor (sp_who2) it shows me as rolling back.
sp_whoisactive would also tell you what it's waiting on. I can't help unless you give me more info...
July 25, 2011 at 7:50 am
here is the waitinfo ..
(174ms)PAGEIOLATCH_EX:DBNAME:50(*)
July 25, 2011 at 8:00 am
Tara-1044200 (7/25/2011)
here is the waitinfo ..(174ms)PAGEIOLATCH_EX:DBNAME:50(*)
Keep checking this figure. That's only 1 / 6 of a second wait. So it's a pretty safe bet that the rollback is still going.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply