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') 

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

  • Are you the same user who posted the previous question from 8 days ago? If yes, you may want to ask to merge your accounts. – Max Vernon Nov 9 '18 at 18:35
  • 2
    Yeah that query is unreliable precisely because if a query has locks on DB1 and DB2, sys.dm_exec_sessions can only show one of them, and it may not be the one that matters. In the future, avoid KILL, always use ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE; - requires no looping and doesn't care what sys.dm_exec_sessions says... – Aaron Bertrand Nov 9 '18 at 19:04

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.

|improve this answer|||||
  • Thanks! Yes, I have tested the backup on DEV. Looks like a reboot is in order! – SQLOakland Nov 9 '18 at 18:45
  • @SQLOakland - did you get this issue resolved? – Max Vernon Nov 13 '18 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.