Sql-server – Killed Backup in Rollback State for Days

backupsql serversql-server-2012

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…

Best Answer

Since the backup is waiting on a CMEMTHREAD, that is a critical section waiting for a memory allocation, it is likely that a restart of the service will either allow recovery to start on the database, or for the database to be marked "suspect", at which point you could drop and restore from backup. Recovery on a 70MB database, assuming the log file isn't many many gigabytes, should be nearly instantaneous.

As always, before doing anything of this nature, you need to ensure you have verified-good backups of critical databases on this instance - by "verified", I mean you've restored them onto some other server (or your workstation), to ensure you can actually perform a restore. There is nothing like getting free advice from the internet only to find you've lost everything.

As an aside, you should ensure you have the most recent service pack and cumulative update installed on your SQL Server; see SQLServerBuilds for details.

Related Question