Killed Backup in Rollback State for Days
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 backup
edited Nov 10 at 17:36
Goncalo Peres
133114
asked Nov 9 at 17:55
SQLOakland
261
add a comment |
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 backup
edited Nov 10 at 17:36
Goncalo Peres
133114
asked Nov 9 at 17:55
SQLOakland
261
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 at 18:35
2
Yeah that query is unreliable precisely because if a query has locks on DB1 and DB2,sys.dm_exec_sessionscan only show one of them, and it may not be the one that matters. In the future, avoidKILL, always useALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;- requires no looping and doesn't care whatsys.dm_exec_sessionssays...
– Aaron Bertrand♦
Nov 9 at 19:04
add a comment |
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 backup
edited Nov 10 at 17:36
Goncalo Peres
133114
asked Nov 9 at 17:55
SQLOakland
261
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 backup
sql-server sql-server-2012 backup
edited Nov 10 at 17:36
Goncalo Peres
133114
asked Nov 9 at 17:55
SQLOakland
261
edited Nov 10 at 17:36
Goncalo Peres
133114
asked Nov 9 at 17:55
SQLOakland
261
edited Nov 10 at 17:36
Goncalo Peres
133114
edited Nov 10 at 17:36
Goncalo Peres
133114
edited Nov 10 at 17:36
Goncalo Peres
133114
133114
asked Nov 9 at 17:55
SQLOakland
261
asked Nov 9 at 17:55
SQLOakland
261
asked Nov 9 at 17:55
SQLOakland
261
261
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 at 18:35
2
Yeah that query is unreliable precisely because if a query has locks on DB1 and DB2,sys.dm_exec_sessionscan only show one of them, and it may not be the one that matters. In the future, avoidKILL, always useALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;- requires no looping and doesn't care whatsys.dm_exec_sessionssays...
– Aaron Bertrand♦
Nov 9 at 19:04
add a comment |
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 at 18:35
2
Yeah that query is unreliable precisely because if a query has locks on DB1 and DB2,sys.dm_exec_sessionscan only show one of them, and it may not be the one that matters. In the future, avoidKILL, always useALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;- requires no looping and doesn't care whatsys.dm_exec_sessionssays...
– Aaron Bertrand♦
Nov 9 at 19:04
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 at 18:35
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 at 18:35
2
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 at 19:04
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 at 19:04
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Nov 9 at 18:37
Max Vernon
49.7k13111219
Thanks! Yes, I have tested the backup on DEV. Looks like a reboot is in order!
– SQLOakland
Nov 9 at 18:45
@SQLOakland - did you get this issue resolved?
– Max Vernon
Nov 13 at 17:27
add a comment |
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',
autoActivateHeartbeat: false,
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
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 9 at 18:37
Max Vernon
49.7k13111219
Thanks! Yes, I have tested the backup on DEV. Looks like a reboot is in order!
– SQLOakland
Nov 9 at 18:45
@SQLOakland - did you get this issue resolved?
– Max Vernon
Nov 13 at 17:27
add a comment |
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.
answered Nov 9 at 18:37
Max Vernon
49.7k13111219
Thanks! Yes, I have tested the backup on DEV. Looks like a reboot is in order!
– SQLOakland
Nov 9 at 18:45
@SQLOakland - did you get this issue resolved?
– Max Vernon
Nov 13 at 17:27
add a comment |
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.
answered Nov 9 at 18:37
Max Vernon
49.7k13111219
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.
answered Nov 9 at 18:37
Max Vernon
49.7k13111219
edited Nov 9 at 18:51
answered Nov 9 at 18:37
Max Vernon
49.7k13111219
answered Nov 9 at 18:37
Max Vernon
49.7k13111219
answered Nov 9 at 18:37
Max Vernon
49.7k13111219
49.7k13111219
Thanks! Yes, I have tested the backup on DEV. Looks like a reboot is in order!
– SQLOakland
Nov 9 at 18:45
@SQLOakland - did you get this issue resolved?
– Max Vernon
Nov 13 at 17:27
add a comment |
Thanks! Yes, I have tested the backup on DEV. Looks like a reboot is in order!
– SQLOakland
Nov 9 at 18:45
@SQLOakland - did you get this issue resolved?
– Max Vernon
Nov 13 at 17:27
Thanks! Yes, I have tested the backup on DEV. Looks like a reboot is in order!
– SQLOakland
Nov 9 at 18:45
Thanks! Yes, I have tested the backup on DEV. Looks like a reboot is in order!
– SQLOakland
Nov 9 at 18:45
@SQLOakland - did you get this issue resolved?
– Max Vernon
Nov 13 at 17:27
@SQLOakland - did you get this issue resolved?
– Max Vernon
Nov 13 at 17:27
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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 at 18:35
2
Yeah that query is unreliable precisely because if a query has locks on DB1 and DB2,
sys.dm_exec_sessionscan only show one of them, and it may not be the one that matters. In the future, avoidKILL, always useALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;- requires no looping and doesn't care whatsys.dm_exec_sessionssays...– Aaron Bertrand♦
Nov 9 at 19:04