Killed Backup in Rollback State for Days

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
1
down vote

favorite












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






sql-server sql-server-2012







share|improve this question























  • How did you kill the backup job? Why? How exactly did you try "killing all sessions in the database"? Can you restore a backup of the database but restore it as a different name?
    – Aaron Bertrand♦
    2 hours ago










  • I killed it using kill spid. I used a script to kill all the sessions. The database is tied to an application and needs to keep same name. Thanks so much...
    – SQLOakland
    2 hours ago










  • What script to kill all the sessions? Did it use some unreliable method of tying a session to a single, specific database? (These are unreliable because a session can be holding locks in multiple databases, but only one is reported.) What is the current wait_type for the spid that is executing the rollback? (Check sys.dm_exec_requests.)
    – Aaron Bertrand♦
    2 hours ago










  • 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)
    – SQLOakland
    2 hours ago







  • 1




    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. It may not be the one that matters. In the future, avoid KILL, always use ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    – Aaron Bertrand♦
    1 hour ago

















up vote
1
down vote

favorite












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






sql-server sql-server-2012







share|improve this question























  • How did you kill the backup job? Why? How exactly did you try "killing all sessions in the database"? Can you restore a backup of the database but restore it as a different name?
    – Aaron Bertrand♦
    2 hours ago










  • I killed it using kill spid. I used a script to kill all the sessions. The database is tied to an application and needs to keep same name. Thanks so much...
    – SQLOakland
    2 hours ago










  • What script to kill all the sessions? Did it use some unreliable method of tying a session to a single, specific database? (These are unreliable because a session can be holding locks in multiple databases, but only one is reported.) What is the current wait_type for the spid that is executing the rollback? (Check sys.dm_exec_requests.)
    – Aaron Bertrand♦
    2 hours ago










  • 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)
    – SQLOakland
    2 hours ago







  • 1




    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. It may not be the one that matters. In the future, avoid KILL, always use ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    – Aaron Bertrand♦
    1 hour ago













up vote
1
down vote

favorite









up vote
1
down vote

favorite











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






sql-server sql-server-2012







share|improve this question















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






sql-server sql-server-2012




sql-server sql-server-2012






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 40 mins ago









Max Vernon

49.8k13110214




49.8k13110214










asked 2 hours ago









SQLOakland

211




211











  • How did you kill the backup job? Why? How exactly did you try "killing all sessions in the database"? Can you restore a backup of the database but restore it as a different name?
    – Aaron Bertrand♦
    2 hours ago










  • I killed it using kill spid. I used a script to kill all the sessions. The database is tied to an application and needs to keep same name. Thanks so much...
    – SQLOakland
    2 hours ago










  • What script to kill all the sessions? Did it use some unreliable method of tying a session to a single, specific database? (These are unreliable because a session can be holding locks in multiple databases, but only one is reported.) What is the current wait_type for the spid that is executing the rollback? (Check sys.dm_exec_requests.)
    – Aaron Bertrand♦
    2 hours ago










  • 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)
    – SQLOakland
    2 hours ago







  • 1




    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. It may not be the one that matters. In the future, avoid KILL, always use ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    – Aaron Bertrand♦
    1 hour ago

















  • How did you kill the backup job? Why? How exactly did you try "killing all sessions in the database"? Can you restore a backup of the database but restore it as a different name?
    – Aaron Bertrand♦
    2 hours ago










  • I killed it using kill spid. I used a script to kill all the sessions. The database is tied to an application and needs to keep same name. Thanks so much...
    – SQLOakland
    2 hours ago










  • What script to kill all the sessions? Did it use some unreliable method of tying a session to a single, specific database? (These are unreliable because a session can be holding locks in multiple databases, but only one is reported.) What is the current wait_type for the spid that is executing the rollback? (Check sys.dm_exec_requests.)
    – Aaron Bertrand♦
    2 hours ago










  • 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)
    – SQLOakland
    2 hours ago







  • 1




    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. It may not be the one that matters. In the future, avoid KILL, always use ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    – Aaron Bertrand♦
    1 hour ago
















How did you kill the backup job? Why? How exactly did you try "killing all sessions in the database"? Can you restore a backup of the database but restore it as a different name?
– Aaron Bertrand♦
2 hours ago




How did you kill the backup job? Why? How exactly did you try "killing all sessions in the database"? Can you restore a backup of the database but restore it as a different name?
– Aaron Bertrand♦
2 hours ago












I killed it using kill spid. I used a script to kill all the sessions. The database is tied to an application and needs to keep same name. Thanks so much...
– SQLOakland
2 hours ago




I killed it using kill spid. I used a script to kill all the sessions. The database is tied to an application and needs to keep same name. Thanks so much...
– SQLOakland
2 hours ago












What script to kill all the sessions? Did it use some unreliable method of tying a session to a single, specific database? (These are unreliable because a session can be holding locks in multiple databases, but only one is reported.) What is the current wait_type for the spid that is executing the rollback? (Check sys.dm_exec_requests.)
– Aaron Bertrand♦
2 hours ago




What script to kill all the sessions? Did it use some unreliable method of tying a session to a single, specific database? (These are unreliable because a session can be holding locks in multiple databases, but only one is reported.) What is the current wait_type for the spid that is executing the rollback? (Check sys.dm_exec_requests.)
– Aaron Bertrand♦
2 hours ago












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)
– SQLOakland
2 hours ago





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)
– SQLOakland
2 hours ago





1




1




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. It may not be the one that matters. In the future, avoid KILL, always use ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
– Aaron Bertrand♦
1 hour ago





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. It may not be the one that matters. In the future, avoid KILL, always use ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
– Aaron Bertrand♦
1 hour ago











1 Answer
1






active

oldest

votes

















up vote
5
down vote













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.






share|improve this answer






















  • Thanks! Yes, I have tested the backup on DEV. Looks like a reboot is in order!
    – SQLOakland
    1 hour ago










Your Answer








StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);













 

draft saved


draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222212%2fkilled-backup-in-rollback-state-for-days%23new-answer', 'question_page');

);

Post as a guest

















1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes








up vote
5
down vote













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.






share|improve this answer






















  • Thanks! Yes, I have tested the backup on DEV. Looks like a reboot is in order!
    – SQLOakland
    1 hour ago














up vote
5
down vote













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.






share|improve this answer






















  • Thanks! Yes, I have tested the backup on DEV. Looks like a reboot is in order!
    – SQLOakland
    1 hour ago












up vote
5
down vote










up vote
5
down vote









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.






share|improve this 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.







share|improve this answer














share|improve this answer



share|improve this answer








edited 1 hour ago

























answered 1 hour ago









Max Vernon

49.8k13110214




49.8k13110214











  • Thanks! Yes, I have tested the backup on DEV. Looks like a reboot is in order!
    – SQLOakland
    1 hour ago
















  • Thanks! Yes, I have tested the backup on DEV. Looks like a reboot is in order!
    – SQLOakland
    1 hour ago















Thanks! Yes, I have tested the backup on DEV. Looks like a reboot is in order!
– SQLOakland
1 hour ago




Thanks! Yes, I have tested the backup on DEV. Looks like a reboot is in order!
– SQLOakland
1 hour ago

















 

draft saved


draft discarded















































 


draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222212%2fkilled-backup-in-rollback-state-for-days%23new-answer', 'question_page');

);

Post as a guest
































































Comments

Post a Comment

Popular posts from this blog

What does second last employer means? [closed]

Image
Clash Royale CLAN TAG #URR8PPP .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0; up vote -3 down vote favorite Say, for example I have worked in the following companies, Company A => 2010 - 2011 Company B => 2011 - 2012 Company C => 2012 - Till Date Company D => Have offer Now assume Company D is asking to submit documents from my second last employer. So from the list of companies given, which company would be qualified as second last employer? software-industry employer share | improve this question asked Aug 10 '14 at 5:16 Rajaprabhu Aravindasamy 559 1 6 13 closed as off-topic by Jim G., jmort253 ♦ Aug 11 '14 at 1:58 This question appears to be off-topic. The users who voted to close gave this specific reason: "Questions seeking advice on company-specific regulations, agreements, or policies should be directed to your manager or HR department. Questions that address...
Read more

List of Gilmore Girls characters

Image
The season three cast—(Back row): Lane, Michel, Paris, Emily, Richard, Sookie, Miss Patty, Kirk; Front row: Jess, Luke, Lorelai, Rory, Dean This is a list of characters for the comedy-drama television series Gilmore Girls . Contents 1 Main characters 1.1 Lorelai Gilmore 1.2 Rory Gilmore 1.3 Sookie St. James 1.4 Lane Kim 1.5 Michel Gerard 1.6 Luke Danes 1.7 Emily Gilmore 1.8 Richard Gilmore 1.9 Paris Geller 1.10 Dean Forester 1.11 Jess Mariano 1.12 Kirk Gleason 1.13 Jason Stiles 1.14 Logan Huntzberger 2 Major recurring characters 2.1 Christopher Hayden 2.2 Jackson Belleville 2.3 Mrs. Kim 2.4 Tristin DuGray 2.5 Max Medina 2.6 Taylor Doose 2.7 Dave Rygalski 2.8 Marty 3 Recurring characters 3.1 Stars Hollow 3.2 Chilton 3.3 Yale 3.4 Other 4 Notable guest stars 5 Unseen characters 6 Notes 7 References Main characters Actor Character Appearances Season 1 Season 2 Season 3 Season 4 Season 5 Season 6 Season 7 A Year in the Life Lauren Graham Lorelai Gilmore...
Read more

Confectionery

Image
Prepared foods made with a lot of sugar or other cabohydrates "Sweetmeat" redirects here. For the racehorse, see Sweetmeat (horse). Not to be confused with Sweetbread. This Kransekake is a traditional Scandinavian baker's confection. Confectionery is the art of making confections , which are food items that are rich in sugar and carbohydrates. Exact definitions are difficult. [1] In general, though, confectionery is divided into two broad and somewhat overlapping categories, bakers' confections and sugar confections. [2] Bakers' confectionery, also called flour confections , includes principally sweet pastries, cakes, and similar baked goods. Sugar confectionery includes candies ( sweets in British English), candied nuts, chocolates, chewing gum, bubble gum, pastillage, and other confections that are made primarily of sugar. In some cases, chocolate confections (confections made of chocolate) are treated as a separate category, as are sugar-free versions of ...
Read more