SQLTeam.com | Weblogs | Forums

SQL Server 2012 Killed Backup in Rollback State for Days


#1

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...


#2

Can you stop the instance? If so, you could force SQL to lose the db by (1) stop SQL; (2) delete (or move) the existing db files from their current folder; (3) restart SQL. SQL will "see" that the db is not there and should cancel recovery. You can then restore that same db name from a backup, as normal.