I killed backup job of an 80 mb database which has been rolling back for days... I tried to take the database into single user mode, but it too hung in rollback state. I tried killing all sessions in the database, but still cannot drop it. I am allowed to drop and restore it if need be. HOW DO I SOLVE this issue??? It is suspended. No blocking.
The wait type is CMEMTHREAD
I used this script to kill all sessions:
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';
' FROM sys.dm_exec_sessions
WHERE database_id = db_id('fpddev92') exec(@kill)
Afraid if I restart the service it will pickup the rollback upon startup.,
ANY help is GREATLY appreciated! I have never seen anything like this in my life...