I killed a 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 that too hung in rollback state.
I tried killing all sessions connected to the database, but still cannot drop it. I used this script to do the kill:
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)
The task's most recent wait type is CMEMTHREAD
. I cannot restore the database with a different name since there is an application using it that has a hard-coded database name within the app.
I am allowed to drop and restore the database if need be. How do I solve this issue??? It is suspended. No blocking.
I'm afraid if I restart the service it will simply pickup the rollback upon startup.
Any help is greatly appreciated! I have never seen anything like this in my life...