How to immediately terminate a SPID with a very long rollback time estimated?
I have a script to restore a database from a backup. Prior to the restore command, it has:
set single_user with rollback immediate
The script seemed to be hanging here. I used sp_who2 to find out a SPID number 70 was in ROLLBACK state. I then used kill 70 with statusonly to obtain estimated time remaining - and it translates to more than 20,000 hours.
How can I arrest this rollback, even if it means the DB ends up in a transactionally inconsistent state? (I don't really care, as I just want to restore it from a backup.) Is my only option to shut down the server (and impact other DBs on that server)?
sql-server restore rollback kill
asked Nov 8 '18 at 23:43
youcantryreachingmeyoucantryreachingme
3337
bumped to the homepage by Community♦ 16 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have a script to restore a database from a backup. Prior to the restore command, it has:
set single_user with rollback immediate
The script seemed to be hanging here. I used sp_who2 to find out a SPID number 70 was in ROLLBACK state. I then used kill 70 with statusonly to obtain estimated time remaining - and it translates to more than 20,000 hours.
How can I arrest this rollback, even if it means the DB ends up in a transactionally inconsistent state? (I don't really care, as I just want to restore it from a backup.) Is my only option to shut down the server (and impact other DBs on that server)?
sql-server restore rollback kill
asked Nov 8 '18 at 23:43
youcantryreachingmeyoucantryreachingme
3337
bumped to the homepage by Community♦ 16 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
If you dont care about current db, rename this to old and restore the db you wanted. This should work or taking it offline if the spid is least to be cared for.. this is to avoid sql restart.
– BeginnerDBA
Nov 9 '18 at 0:01
I couldn't rename the db data file as it was in use. I read elsewhere that restarting the server means the rolling back transaction will take even longer to roll back after restart. Regardless, I restarted the server then found no SPIDS in rollback state. This appears to have freed up the DB but I don't understand what would have happened to the transactional consistency of that rolling back SPID. Am now restoring from backup.
– youcantryreachingme
Nov 9 '18 at 0:09
add a comment |
I have a script to restore a database from a backup. Prior to the restore command, it has:
set single_user with rollback immediate
The script seemed to be hanging here. I used sp_who2 to find out a SPID number 70 was in ROLLBACK state. I then used kill 70 with statusonly to obtain estimated time remaining - and it translates to more than 20,000 hours.
How can I arrest this rollback, even if it means the DB ends up in a transactionally inconsistent state? (I don't really care, as I just want to restore it from a backup.) Is my only option to shut down the server (and impact other DBs on that server)?
sql-server restore rollback kill
asked Nov 8 '18 at 23:43
youcantryreachingmeyoucantryreachingme
3337
I have a script to restore a database from a backup. Prior to the restore command, it has:
set single_user with rollback immediate
The script seemed to be hanging here. I used sp_who2 to find out a SPID number 70 was in ROLLBACK state. I then used kill 70 with statusonly to obtain estimated time remaining - and it translates to more than 20,000 hours.
How can I arrest this rollback, even if it means the DB ends up in a transactionally inconsistent state? (I don't really care, as I just want to restore it from a backup.) Is my only option to shut down the server (and impact other DBs on that server)?
sql-server restore rollback kill
sql-server restore rollback kill
asked Nov 8 '18 at 23:43
youcantryreachingmeyoucantryreachingme
3337
asked Nov 8 '18 at 23:43
youcantryreachingmeyoucantryreachingme
3337
asked Nov 8 '18 at 23:43
youcantryreachingmeyoucantryreachingme
3337
asked Nov 8 '18 at 23:43
youcantryreachingmeyoucantryreachingme
3337
asked Nov 8 '18 at 23:43
youcantryreachingmeyoucantryreachingme
3337
3337
bumped to the homepage by Community♦ 16 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 16 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
If you dont care about current db, rename this to old and restore the db you wanted. This should work or taking it offline if the spid is least to be cared for.. this is to avoid sql restart.
– BeginnerDBA
Nov 9 '18 at 0:01
I couldn't rename the db data file as it was in use. I read elsewhere that restarting the server means the rolling back transaction will take even longer to roll back after restart. Regardless, I restarted the server then found no SPIDS in rollback state. This appears to have freed up the DB but I don't understand what would have happened to the transactional consistency of that rolling back SPID. Am now restoring from backup.
– youcantryreachingme
Nov 9 '18 at 0:09
add a comment |
If you dont care about current db, rename this to old and restore the db you wanted. This should work or taking it offline if the spid is least to be cared for.. this is to avoid sql restart.
– BeginnerDBA
Nov 9 '18 at 0:01
I couldn't rename the db data file as it was in use. I read elsewhere that restarting the server means the rolling back transaction will take even longer to roll back after restart. Regardless, I restarted the server then found no SPIDS in rollback state. This appears to have freed up the DB but I don't understand what would have happened to the transactional consistency of that rolling back SPID. Am now restoring from backup.
– youcantryreachingme
Nov 9 '18 at 0:09
If you dont care about current db, rename this to old and restore the db you wanted. This should work or taking it offline if the spid is least to be cared for.. this is to avoid sql restart.
– BeginnerDBA
Nov 9 '18 at 0:01
If you dont care about current db, rename this to old and restore the db you wanted. This should work or taking it offline if the spid is least to be cared for.. this is to avoid sql restart.
– BeginnerDBA
Nov 9 '18 at 0:01
I couldn't rename the db data file as it was in use. I read elsewhere that restarting the server means the rolling back transaction will take even longer to roll back after restart. Regardless, I restarted the server then found no SPIDS in rollback state. This appears to have freed up the DB but I don't understand what would have happened to the transactional consistency of that rolling back SPID. Am now restoring from backup.
– youcantryreachingme
Nov 9 '18 at 0:09
I couldn't rename the db data file as it was in use. I read elsewhere that restarting the server means the rolling back transaction will take even longer to roll back after restart. Regardless, I restarted the server then found no SPIDS in rollback state. This appears to have freed up the DB but I don't understand what would have happened to the transactional consistency of that rolling back SPID. Am now restoring from backup.
– youcantryreachingme
Nov 9 '18 at 0:09
add a comment |
1 Answer
1
active
oldest
votes
Let that thing rollback. There's nothing you can do to stop it or speed it up. If you shut down the server or "trip" on the power cord, the rollback will just start back up again when you turn the server back on.
In any case, if you can restore as a different name, you don't need to rename the data file. First, see what files you have:
RESTORE FILELISTONLY FROM DISK = 'C:pathfile.bak';
It will indicate two or more files. You need to map those into the below. To find out what the current data path is, in case the backup is from a different instance, you can grab it from:
EXEC master.sys.sp_helpfile;
Then the restore command is like this:
RESTORE DATABASE [new_name_here]
FROM DISK = 'C:pathfile.bak'
WITH MOVE Logical_Data_File_Name TO 'C:some other pathsome other name.mdf',
MOVE Logical_Log__File_Name TO 'C:some other pathsome other name.ldf';
answered Nov 9 '18 at 0:16
Aaron Bertrand♦Aaron Bertrand
152k18292490
Let it rollback when it's estimating that will take 3 years? Somehow I've recovered it, but that did require a restart of the server (after which there were no SPIDS in rollback state any longer).
– youcantryreachingme
Nov 9 '18 at 0:38
1
That's great that that worked this time, but it won't always. Forcing a restart could put you back at the start of that rollback process, or worse. What if that spid also had locks in another database, one that you do care about, and now you put that database in jeopardy too? That other database could be inaccessible for as long or longer, or it could have gone into suspect or become corrupt. Ctrl + Alt +Del or pulling the power cord works great, until it doesn't, and there is no undo.
– Aaron Bertrand♦
Nov 9 '18 at 0:42
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%2f222145%2fhow-to-immediately-terminate-a-spid-with-a-very-long-rollback-time-estimated%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
Let that thing rollback. There's nothing you can do to stop it or speed it up. If you shut down the server or "trip" on the power cord, the rollback will just start back up again when you turn the server back on.
In any case, if you can restore as a different name, you don't need to rename the data file. First, see what files you have:
RESTORE FILELISTONLY FROM DISK = 'C:pathfile.bak';
It will indicate two or more files. You need to map those into the below. To find out what the current data path is, in case the backup is from a different instance, you can grab it from:
EXEC master.sys.sp_helpfile;
Then the restore command is like this:
RESTORE DATABASE [new_name_here]
FROM DISK = 'C:pathfile.bak'
WITH MOVE Logical_Data_File_Name TO 'C:some other pathsome other name.mdf',
MOVE Logical_Log__File_Name TO 'C:some other pathsome other name.ldf';
answered Nov 9 '18 at 0:16
Aaron Bertrand♦Aaron Bertrand
152k18292490
Let it rollback when it's estimating that will take 3 years? Somehow I've recovered it, but that did require a restart of the server (after which there were no SPIDS in rollback state any longer).
– youcantryreachingme
Nov 9 '18 at 0:38
1
That's great that that worked this time, but it won't always. Forcing a restart could put you back at the start of that rollback process, or worse. What if that spid also had locks in another database, one that you do care about, and now you put that database in jeopardy too? That other database could be inaccessible for as long or longer, or it could have gone into suspect or become corrupt. Ctrl + Alt +Del or pulling the power cord works great, until it doesn't, and there is no undo.
– Aaron Bertrand♦
Nov 9 '18 at 0:42
add a comment |
Let that thing rollback. There's nothing you can do to stop it or speed it up. If you shut down the server or "trip" on the power cord, the rollback will just start back up again when you turn the server back on.
In any case, if you can restore as a different name, you don't need to rename the data file. First, see what files you have:
RESTORE FILELISTONLY FROM DISK = 'C:pathfile.bak';
It will indicate two or more files. You need to map those into the below. To find out what the current data path is, in case the backup is from a different instance, you can grab it from:
EXEC master.sys.sp_helpfile;
Then the restore command is like this:
RESTORE DATABASE [new_name_here]
FROM DISK = 'C:pathfile.bak'
WITH MOVE Logical_Data_File_Name TO 'C:some other pathsome other name.mdf',
MOVE Logical_Log__File_Name TO 'C:some other pathsome other name.ldf';
answered Nov 9 '18 at 0:16
Aaron Bertrand♦Aaron Bertrand
152k18292490
Let it rollback when it's estimating that will take 3 years? Somehow I've recovered it, but that did require a restart of the server (after which there were no SPIDS in rollback state any longer).
– youcantryreachingme
Nov 9 '18 at 0:38
1
That's great that that worked this time, but it won't always. Forcing a restart could put you back at the start of that rollback process, or worse. What if that spid also had locks in another database, one that you do care about, and now you put that database in jeopardy too? That other database could be inaccessible for as long or longer, or it could have gone into suspect or become corrupt. Ctrl + Alt +Del or pulling the power cord works great, until it doesn't, and there is no undo.
– Aaron Bertrand♦
Nov 9 '18 at 0:42
add a comment |
Let that thing rollback. There's nothing you can do to stop it or speed it up. If you shut down the server or "trip" on the power cord, the rollback will just start back up again when you turn the server back on.
In any case, if you can restore as a different name, you don't need to rename the data file. First, see what files you have:
RESTORE FILELISTONLY FROM DISK = 'C:pathfile.bak';
It will indicate two or more files. You need to map those into the below. To find out what the current data path is, in case the backup is from a different instance, you can grab it from:
EXEC master.sys.sp_helpfile;
Then the restore command is like this:
RESTORE DATABASE [new_name_here]
FROM DISK = 'C:pathfile.bak'
WITH MOVE Logical_Data_File_Name TO 'C:some other pathsome other name.mdf',
MOVE Logical_Log__File_Name TO 'C:some other pathsome other name.ldf';
answered Nov 9 '18 at 0:16
Aaron Bertrand♦Aaron Bertrand
152k18292490
Let that thing rollback. There's nothing you can do to stop it or speed it up. If you shut down the server or "trip" on the power cord, the rollback will just start back up again when you turn the server back on.
In any case, if you can restore as a different name, you don't need to rename the data file. First, see what files you have:
RESTORE FILELISTONLY FROM DISK = 'C:pathfile.bak';
It will indicate two or more files. You need to map those into the below. To find out what the current data path is, in case the backup is from a different instance, you can grab it from:
EXEC master.sys.sp_helpfile;
Then the restore command is like this:
RESTORE DATABASE [new_name_here]
FROM DISK = 'C:pathfile.bak'
WITH MOVE Logical_Data_File_Name TO 'C:some other pathsome other name.mdf',
MOVE Logical_Log__File_Name TO 'C:some other pathsome other name.ldf';
answered Nov 9 '18 at 0:16
Aaron Bertrand♦Aaron Bertrand
152k18292490
answered Nov 9 '18 at 0:16
Aaron Bertrand♦Aaron Bertrand
152k18292490
answered Nov 9 '18 at 0:16
Aaron Bertrand♦Aaron Bertrand
152k18292490
answered Nov 9 '18 at 0:16
Aaron Bertrand♦Aaron Bertrand
152k18292490
152k18292490
Let it rollback when it's estimating that will take 3 years? Somehow I've recovered it, but that did require a restart of the server (after which there were no SPIDS in rollback state any longer).
– youcantryreachingme
Nov 9 '18 at 0:38
1
That's great that that worked this time, but it won't always. Forcing a restart could put you back at the start of that rollback process, or worse. What if that spid also had locks in another database, one that you do care about, and now you put that database in jeopardy too? That other database could be inaccessible for as long or longer, or it could have gone into suspect or become corrupt. Ctrl + Alt +Del or pulling the power cord works great, until it doesn't, and there is no undo.
– Aaron Bertrand♦
Nov 9 '18 at 0:42
add a comment |
Let it rollback when it's estimating that will take 3 years? Somehow I've recovered it, but that did require a restart of the server (after which there were no SPIDS in rollback state any longer).
– youcantryreachingme
Nov 9 '18 at 0:38
1
That's great that that worked this time, but it won't always. Forcing a restart could put you back at the start of that rollback process, or worse. What if that spid also had locks in another database, one that you do care about, and now you put that database in jeopardy too? That other database could be inaccessible for as long or longer, or it could have gone into suspect or become corrupt. Ctrl + Alt +Del or pulling the power cord works great, until it doesn't, and there is no undo.
– Aaron Bertrand♦
Nov 9 '18 at 0:42
Let it rollback when it's estimating that will take 3 years? Somehow I've recovered it, but that did require a restart of the server (after which there were no SPIDS in rollback state any longer).
– youcantryreachingme
Nov 9 '18 at 0:38
Let it rollback when it's estimating that will take 3 years? Somehow I've recovered it, but that did require a restart of the server (after which there were no SPIDS in rollback state any longer).
– youcantryreachingme
Nov 9 '18 at 0:38
1
1
That's great that that worked this time, but it won't always. Forcing a restart could put you back at the start of that rollback process, or worse. What if that spid also had locks in another database, one that you do care about, and now you put that database in jeopardy too? That other database could be inaccessible for as long or longer, or it could have gone into suspect or become corrupt. Ctrl + Alt +Del or pulling the power cord works great, until it doesn't, and there is no undo.
– Aaron Bertrand♦
Nov 9 '18 at 0:42
That's great that that worked this time, but it won't always. Forcing a restart could put you back at the start of that rollback process, or worse. What if that spid also had locks in another database, one that you do care about, and now you put that database in jeopardy too? That other database could be inaccessible for as long or longer, or it could have gone into suspect or become corrupt. Ctrl + Alt +Del or pulling the power cord works great, until it doesn't, and there is no undo.
– Aaron Bertrand♦
Nov 9 '18 at 0:42
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.
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%2f222145%2fhow-to-immediately-terminate-a-spid-with-a-very-long-rollback-time-estimated%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
If you dont care about current db, rename this to old and restore the db you wanted. This should work or taking it offline if the spid is least to be cared for.. this is to avoid sql restart.
– BeginnerDBA
Nov 9 '18 at 0:01
I couldn't rename the db data file as it was in use. I read elsewhere that restarting the server means the rolling back transaction will take even longer to roll back after restart. Regardless, I restarted the server then found no SPIDS in rollback state. This appears to have freed up the DB but I don't understand what would have happened to the transactional consistency of that rolling back SPID. Am now restoring from backup.
– youcantryreachingme
Nov 9 '18 at 0:09