March 4, 2015 at 10:18 am
I was running a stored procedure it was suspended for about 11 hours so I decided to kill it now its in Killed/Rollback stage for 12 hours and when check the status of roll back it says "Estimated rollback completion: 0%. Estimated time remaining: 0 seconds." its using up CPUTIME 380000 and DiskIO 970000. How to do I stop this completely
March 4, 2015 at 11:06 am
smoham22 (3/4/2015)
I was running a stored procedure it was suspended for about 11 hours so I decided to kill it now its in Killed/Rollback stage for 12 hours and when check the status of roll back it says "Estimated rollback completion: 0%. Estimated time remaining: 0 seconds." its using up CPUTIME 380000 and DiskIO 970000. How to do I stop this completely
If it's rolling back, it's rolling back, there is not much you can do. Even if you reboot SQL server and if there are uncommitted transactions, the SQL server itself will initiate the rollback again.
Run this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
DB_NAME(er.[database_id]) [DatabaseName]
,er.[session_id] AS [SessionID]
,er.[command] AS [CommandType]
,est.[text] [StatementText]
,er.[status] AS [Status]
,CONVERT(DECIMAL(5, 2), er.[percent_complete]) AS [Complete_Percent]
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m]
,er.[last_wait_type] [LastWait]
,er.[wait_resource] [CurrentWait]
FROM sys.dm_exec_requests AS er
INNER JOIN sys.dm_exec_sessions AS es ON er.[session_id] = es.[session_id]
CROSS APPLY sys.dm_exec_sql_text(er.[sql_handle]) est
It gives you a ballpark number of how much still remains.
March 4, 2015 at 1:52 pm
The 0% rollbacks will never end because there's nothing to do. If the SPID isn't consuming CPU or a shedload of data, you might leave it for the next reboot. If it was because you did something that caused an external call, such as a call to xp_CmdShell, we can fix that without a reboot. The question is, what did the proc in question actually do?
--Jeff Moden
March 4, 2015 at 2:46 pm
Its a scrubbing job. it takes a backup from production and restores it on development server and scrubs out the sensitive data for development.
spid went away after the server got rebooted but every time i kick off the job it shows it gets suspended after the database gets restored.
March 11, 2015 at 7:51 pm
smoham22 (3/4/2015)
Its a scrubbing job. it takes a backup from production and restores it on development server and scrubs out the sensitive data for development.spid went away after the server got rebooted but every time i kick off the job it shows it gets suspended after the database gets restored.
Sorry for the late reply. I guess we'd need to see the stored procedure to even begin to try to solve this problem (if you still have it).
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply