SQL Server 2012 Killed Backup in Rollback State for Days

  • Noelle

    Newbie

    Points: 6

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

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 18856

    Do have got any informational messages in the error log. Can you post error log.

    Muthukkumaran KaliyamoorthyHelping SQL DBAs and Developers >>>SqlserverBlogForum[/url]

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714105

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply