Set database offline with rollback immediate takes an hour to complete
Every night we take our production database offline, detach it, and then re-create it to sever any connections as the start of our backup procedures. The first command is:
USE master
ALTER DATABASE thedb SET OFFLINE WITH ROLLBACK IMMEDIATE
This command used to take 20 seconds to two minutes to complete which was acceptable. For the past few weeks it has been taking 15 minutes to over an hour to complete. There hasn't been any change in database usage that would cause this problem, and it only happens about 3/4 of the time.
As far as I know, we have not settings that would cause this. My understanding was WITH ROLLBACK IMMEDAITE severed all connections and the database goes offline immediately. I have tried investigating the following:
- recovery interval is set to 0 so checkpoints happen every few minutes and only take a second to complete
- Statistics do not update asynchronously
- no large queries are running just before the database goes offline
- checking sp_who2 while the database going offline shows no users connected to it
- the database's log file is very small, ~30 MB
We are currently running SQL Standard 2012 in simple recovery mode with no mirroring. The database is about 300 GB in size.
Any help is appreciated, thank you!
sql-server-2012
asked Mar 9 '16 at 13:12
GUIsGUIs
3114
This question has an open bounty worth +100
reputation from Michael J Swart ending in 7 days.
This question has not received enough attention.
|
show 1 more comment
Every night we take our production database offline, detach it, and then re-create it to sever any connections as the start of our backup procedures. The first command is:
USE master
ALTER DATABASE thedb SET OFFLINE WITH ROLLBACK IMMEDIATE
This command used to take 20 seconds to two minutes to complete which was acceptable. For the past few weeks it has been taking 15 minutes to over an hour to complete. There hasn't been any change in database usage that would cause this problem, and it only happens about 3/4 of the time.
As far as I know, we have not settings that would cause this. My understanding was WITH ROLLBACK IMMEDAITE severed all connections and the database goes offline immediately. I have tried investigating the following:
- recovery interval is set to 0 so checkpoints happen every few minutes and only take a second to complete
- Statistics do not update asynchronously
- no large queries are running just before the database goes offline
- checking sp_who2 while the database going offline shows no users connected to it
- the database's log file is very small, ~30 MB
We are currently running SQL Standard 2012 in simple recovery mode with no mirroring. The database is about 300 GB in size.
Any help is appreciated, thank you!
sql-server-2012
asked Mar 9 '16 at 13:12
GUIsGUIs
3114
This question has an open bounty worth +100
reputation from Michael J Swart ending in 7 days.
This question has not received enough attention.
1
What does the session say it is waiting on?
– dwjv
Mar 9 '16 at 13:18
As in the BlkBy column in sp_who2? It doesn't show it waiting on any sessions. I've also looked at all active locks and it shows as the only lock on the database.
– GUIs
Mar 9 '16 at 14:14
2
Does the messages say that some transactions were rolled back when it completes? What is the reason you need to take the database offline to back it up? If you are looking just to kick the users off during the backup you could use set the access to restricted, as long as the users you are trying to boot aren't in the sysadmin role, they will be blocked from logging in until you set the database back to multi_user.alter database set restricted_user with rollback immediatedo your backups thenalter database set multi_user with rollback immediate
– Aaron
Mar 9 '16 at 14:51
1
@Aaron It shows the following messages:Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.Thank you for the restricted_user suggestion, I will attempt that tonight. It isn't necessary to take the database offline to back up, we just wanted to make sure any transactions and connections are cleared out as we had trouble with uncommitted transactions.
– GUIs
Mar 9 '16 at 15:26
1
@GUIs you can't get the wait type from sp_who2 - you can querysys.dm_exec_requests(wait type) or check the Activity Monitor.
– dwjv
Mar 11 '16 at 9:04
|
show 1 more comment
Every night we take our production database offline, detach it, and then re-create it to sever any connections as the start of our backup procedures. The first command is:
USE master
ALTER DATABASE thedb SET OFFLINE WITH ROLLBACK IMMEDIATE
This command used to take 20 seconds to two minutes to complete which was acceptable. For the past few weeks it has been taking 15 minutes to over an hour to complete. There hasn't been any change in database usage that would cause this problem, and it only happens about 3/4 of the time.
As far as I know, we have not settings that would cause this. My understanding was WITH ROLLBACK IMMEDAITE severed all connections and the database goes offline immediately. I have tried investigating the following:
- recovery interval is set to 0 so checkpoints happen every few minutes and only take a second to complete
- Statistics do not update asynchronously
- no large queries are running just before the database goes offline
- checking sp_who2 while the database going offline shows no users connected to it
- the database's log file is very small, ~30 MB
We are currently running SQL Standard 2012 in simple recovery mode with no mirroring. The database is about 300 GB in size.
Any help is appreciated, thank you!
sql-server-2012
asked Mar 9 '16 at 13:12
GUIsGUIs
3114
Every night we take our production database offline, detach it, and then re-create it to sever any connections as the start of our backup procedures. The first command is:
USE master
ALTER DATABASE thedb SET OFFLINE WITH ROLLBACK IMMEDIATE
This command used to take 20 seconds to two minutes to complete which was acceptable. For the past few weeks it has been taking 15 minutes to over an hour to complete. There hasn't been any change in database usage that would cause this problem, and it only happens about 3/4 of the time.
As far as I know, we have not settings that would cause this. My understanding was WITH ROLLBACK IMMEDAITE severed all connections and the database goes offline immediately. I have tried investigating the following:
- recovery interval is set to 0 so checkpoints happen every few minutes and only take a second to complete
- Statistics do not update asynchronously
- no large queries are running just before the database goes offline
- checking sp_who2 while the database going offline shows no users connected to it
- the database's log file is very small, ~30 MB
We are currently running SQL Standard 2012 in simple recovery mode with no mirroring. The database is about 300 GB in size.
Any help is appreciated, thank you!
sql-server-2012
sql-server-2012
asked Mar 9 '16 at 13:12
GUIsGUIs
3114
asked Mar 9 '16 at 13:12
GUIsGUIs
3114
asked Mar 9 '16 at 13:12
GUIsGUIs
3114
asked Mar 9 '16 at 13:12
GUIsGUIs
3114
asked Mar 9 '16 at 13:12
GUIsGUIs
3114
3114
This question has an open bounty worth +100
reputation from Michael J Swart ending in 7 days.
This question has not received enough attention.
This question has an open bounty worth +100
reputation from Michael J Swart ending in 7 days.
This question has not received enough attention.
1
What does the session say it is waiting on?
– dwjv
Mar 9 '16 at 13:18
As in the BlkBy column in sp_who2? It doesn't show it waiting on any sessions. I've also looked at all active locks and it shows as the only lock on the database.
– GUIs
Mar 9 '16 at 14:14
2
Does the messages say that some transactions were rolled back when it completes? What is the reason you need to take the database offline to back it up? If you are looking just to kick the users off during the backup you could use set the access to restricted, as long as the users you are trying to boot aren't in the sysadmin role, they will be blocked from logging in until you set the database back to multi_user.alter database set restricted_user with rollback immediatedo your backups thenalter database set multi_user with rollback immediate
– Aaron
Mar 9 '16 at 14:51
1
@Aaron It shows the following messages:Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.Thank you for the restricted_user suggestion, I will attempt that tonight. It isn't necessary to take the database offline to back up, we just wanted to make sure any transactions and connections are cleared out as we had trouble with uncommitted transactions.
– GUIs
Mar 9 '16 at 15:26
1
@GUIs you can't get the wait type from sp_who2 - you can querysys.dm_exec_requests(wait type) or check the Activity Monitor.
– dwjv
Mar 11 '16 at 9:04
|
show 1 more comment
1
What does the session say it is waiting on?
– dwjv
Mar 9 '16 at 13:18
As in the BlkBy column in sp_who2? It doesn't show it waiting on any sessions. I've also looked at all active locks and it shows as the only lock on the database.
– GUIs
Mar 9 '16 at 14:14
2
Does the messages say that some transactions were rolled back when it completes? What is the reason you need to take the database offline to back it up? If you are looking just to kick the users off during the backup you could use set the access to restricted, as long as the users you are trying to boot aren't in the sysadmin role, they will be blocked from logging in until you set the database back to multi_user.alter database set restricted_user with rollback immediatedo your backups thenalter database set multi_user with rollback immediate
– Aaron
Mar 9 '16 at 14:51
1
@Aaron It shows the following messages:Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.Thank you for the restricted_user suggestion, I will attempt that tonight. It isn't necessary to take the database offline to back up, we just wanted to make sure any transactions and connections are cleared out as we had trouble with uncommitted transactions.
– GUIs
Mar 9 '16 at 15:26
1
@GUIs you can't get the wait type from sp_who2 - you can querysys.dm_exec_requests(wait type) or check the Activity Monitor.
– dwjv
Mar 11 '16 at 9:04
1
1
What does the session say it is waiting on?
– dwjv
Mar 9 '16 at 13:18
What does the session say it is waiting on?
– dwjv
Mar 9 '16 at 13:18
As in the BlkBy column in sp_who2? It doesn't show it waiting on any sessions. I've also looked at all active locks and it shows as the only lock on the database.
– GUIs
Mar 9 '16 at 14:14
As in the BlkBy column in sp_who2? It doesn't show it waiting on any sessions. I've also looked at all active locks and it shows as the only lock on the database.
– GUIs
Mar 9 '16 at 14:14
2
2
Does the messages say that some transactions were rolled back when it completes? What is the reason you need to take the database offline to back it up? If you are looking just to kick the users off during the backup you could use set the access to restricted, as long as the users you are trying to boot aren't in the sysadmin role, they will be blocked from logging in until you set the database back to multi_user.
alter database set restricted_user with rollback immediate do your backups then alter database set multi_user with rollback immediate– Aaron
Mar 9 '16 at 14:51
Does the messages say that some transactions were rolled back when it completes? What is the reason you need to take the database offline to back it up? If you are looking just to kick the users off during the backup you could use set the access to restricted, as long as the users you are trying to boot aren't in the sysadmin role, they will be blocked from logging in until you set the database back to multi_user.
alter database set restricted_user with rollback immediate do your backups then alter database set multi_user with rollback immediate– Aaron
Mar 9 '16 at 14:51
1
1
@Aaron It shows the following messages:
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. Nonqualified transactions are being rolled back. Estimated rollback completion: 100%. Thank you for the restricted_user suggestion, I will attempt that tonight. It isn't necessary to take the database offline to back up, we just wanted to make sure any transactions and connections are cleared out as we had trouble with uncommitted transactions.– GUIs
Mar 9 '16 at 15:26
@Aaron It shows the following messages:
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. Nonqualified transactions are being rolled back. Estimated rollback completion: 100%. Thank you for the restricted_user suggestion, I will attempt that tonight. It isn't necessary to take the database offline to back up, we just wanted to make sure any transactions and connections are cleared out as we had trouble with uncommitted transactions.– GUIs
Mar 9 '16 at 15:26
1
1
@GUIs you can't get the wait type from sp_who2 - you can query
sys.dm_exec_requests (wait type) or check the Activity Monitor.– dwjv
Mar 11 '16 at 9:04
@GUIs you can't get the wait type from sp_who2 - you can query
sys.dm_exec_requests (wait type) or check the Activity Monitor.– dwjv
Mar 11 '16 at 9:04
|
show 1 more comment
0
active
oldest
votes
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%2f131705%2fset-database-offline-with-rollback-immediate-takes-an-hour-to-complete%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f131705%2fset-database-offline-with-rollback-immediate-takes-an-hour-to-complete%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
1
What does the session say it is waiting on?
– dwjv
Mar 9 '16 at 13:18
As in the BlkBy column in sp_who2? It doesn't show it waiting on any sessions. I've also looked at all active locks and it shows as the only lock on the database.
– GUIs
Mar 9 '16 at 14:14
2
Does the messages say that some transactions were rolled back when it completes? What is the reason you need to take the database offline to back it up? If you are looking just to kick the users off during the backup you could use set the access to restricted, as long as the users you are trying to boot aren't in the sysadmin role, they will be blocked from logging in until you set the database back to multi_user.
alter database set restricted_user with rollback immediatedo your backups thenalter database set multi_user with rollback immediate– Aaron
Mar 9 '16 at 14:51
1
@Aaron It shows the following messages:
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.Thank you for the restricted_user suggestion, I will attempt that tonight. It isn't necessary to take the database offline to back up, we just wanted to make sure any transactions and connections are cleared out as we had trouble with uncommitted transactions.– GUIs
Mar 9 '16 at 15:26
1
@GUIs you can't get the wait type from sp_who2 - you can query
sys.dm_exec_requests(wait type) or check the Activity Monitor.– dwjv
Mar 11 '16 at 9:04