SQL Server 2016 reset recovery mode from 'Full' to 'Simple'
Can anyone help me diagnose why this may have happened? We've got log shipping configured and so the database is in 'Full' recovery mode. This has been working fine for ages, and then randomly it seems to have been reset to 'Simple' recovery, which has broken our log shipping.
This happened about 5 months ago too, we couldn't find any obvious reason for the change so foolishly we chalked it up to a weird one-off and ignored it!
The log viewer shows the change being made, but not how/why it happened. I'm not much of a dba so I don't really know how to dig deeper into finding out what happened and how to prevent it reoccurring.
sql-server log-shipping asked Nov 16 '18 at 10:29
Jordan WallworkJordan Wallwork
1,91611943
add a comment |
Can anyone help me diagnose why this may have happened? We've got log shipping configured and so the database is in 'Full' recovery mode. This has been working fine for ages, and then randomly it seems to have been reset to 'Simple' recovery, which has broken our log shipping.
This happened about 5 months ago too, we couldn't find any obvious reason for the change so foolishly we chalked it up to a weird one-off and ignored it!
The log viewer shows the change being made, but not how/why it happened. I'm not much of a dba so I don't really know how to dig deeper into finding out what happened and how to prevent it reoccurring.
sql-server log-shipping asked Nov 16 '18 at 10:29
Jordan WallworkJordan Wallwork
1,91611943
"This happened about 5 months ago" --......? Talk to your DBA....They won't bite.....much..... Please clarify: it took 5 months for the fact that log shipping was broken to be noticed?
– Mitch Wheat
Nov 16 '18 at 10:49
docs.microsoft.com/en-us/sql/database-engine/log-shipping/…
– Mitch Wheat
Nov 16 '18 at 10:55
1
How are your backups performed? Is it a job or a maintenance plan or something? It seems as if there is some logic like if a backup log is failed then switch to the simple mode and perform the differential one. Check it.
– Denis Rubashkin
Nov 16 '18 at 10:56
@MitchWheat about 5 months ago the recovery mode switched and log shipping failed. I set it back to Full recovery and set log shipping back up from scratch. Unfortunately we're a small operation and aside from a couple of other devs that help me with building our product I'm the dogsbody jack-of-all-trades who has stumbled into the role architect/sysadmin/dba etc!
– Jordan Wallwork
Nov 16 '18 at 11:01
@DenisRubashkin we run a full daily backup, and differential backups every 20 mins. The previous days diffs are deleted after the full backup is taken, and we only spotted this issue this morning, so I don't know whether yesterdays differential backups were interrupted. I can see that the backups from today are running fine though. The backups are run as maintenance plans
– Jordan Wallwork
Nov 16 '18 at 11:05
add a comment |
Can anyone help me diagnose why this may have happened? We've got log shipping configured and so the database is in 'Full' recovery mode. This has been working fine for ages, and then randomly it seems to have been reset to 'Simple' recovery, which has broken our log shipping.
This happened about 5 months ago too, we couldn't find any obvious reason for the change so foolishly we chalked it up to a weird one-off and ignored it!
The log viewer shows the change being made, but not how/why it happened. I'm not much of a dba so I don't really know how to dig deeper into finding out what happened and how to prevent it reoccurring.
sql-server log-shipping asked Nov 16 '18 at 10:29
Jordan WallworkJordan Wallwork
1,91611943
Can anyone help me diagnose why this may have happened? We've got log shipping configured and so the database is in 'Full' recovery mode. This has been working fine for ages, and then randomly it seems to have been reset to 'Simple' recovery, which has broken our log shipping.
This happened about 5 months ago too, we couldn't find any obvious reason for the change so foolishly we chalked it up to a weird one-off and ignored it!
The log viewer shows the change being made, but not how/why it happened. I'm not much of a dba so I don't really know how to dig deeper into finding out what happened and how to prevent it reoccurring.
sql-server log-shipping sql-server log-shipping asked Nov 16 '18 at 10:29
Jordan WallworkJordan Wallwork
1,91611943
asked Nov 16 '18 at 10:29
Jordan WallworkJordan Wallwork
1,91611943
asked Nov 16 '18 at 10:29
Jordan WallworkJordan Wallwork
1,91611943
asked Nov 16 '18 at 10:29
Jordan WallworkJordan Wallwork
1,91611943
asked Nov 16 '18 at 10:29
Jordan WallworkJordan Wallwork
1,91611943
1,91611943
"This happened about 5 months ago" --......? Talk to your DBA....They won't bite.....much..... Please clarify: it took 5 months for the fact that log shipping was broken to be noticed?
– Mitch Wheat
Nov 16 '18 at 10:49
docs.microsoft.com/en-us/sql/database-engine/log-shipping/…
– Mitch Wheat
Nov 16 '18 at 10:55
1
How are your backups performed? Is it a job or a maintenance plan or something? It seems as if there is some logic like if a backup log is failed then switch to the simple mode and perform the differential one. Check it.
– Denis Rubashkin
Nov 16 '18 at 10:56
@MitchWheat about 5 months ago the recovery mode switched and log shipping failed. I set it back to Full recovery and set log shipping back up from scratch. Unfortunately we're a small operation and aside from a couple of other devs that help me with building our product I'm the dogsbody jack-of-all-trades who has stumbled into the role architect/sysadmin/dba etc!
– Jordan Wallwork
Nov 16 '18 at 11:01
@DenisRubashkin we run a full daily backup, and differential backups every 20 mins. The previous days diffs are deleted after the full backup is taken, and we only spotted this issue this morning, so I don't know whether yesterdays differential backups were interrupted. I can see that the backups from today are running fine though. The backups are run as maintenance plans
– Jordan Wallwork
Nov 16 '18 at 11:05
add a comment |
"This happened about 5 months ago" --......? Talk to your DBA....They won't bite.....much..... Please clarify: it took 5 months for the fact that log shipping was broken to be noticed?
– Mitch Wheat
Nov 16 '18 at 10:49
docs.microsoft.com/en-us/sql/database-engine/log-shipping/…
– Mitch Wheat
Nov 16 '18 at 10:55
1
How are your backups performed? Is it a job or a maintenance plan or something? It seems as if there is some logic like if a backup log is failed then switch to the simple mode and perform the differential one. Check it.
– Denis Rubashkin
Nov 16 '18 at 10:56
@MitchWheat about 5 months ago the recovery mode switched and log shipping failed. I set it back to Full recovery and set log shipping back up from scratch. Unfortunately we're a small operation and aside from a couple of other devs that help me with building our product I'm the dogsbody jack-of-all-trades who has stumbled into the role architect/sysadmin/dba etc!
– Jordan Wallwork
Nov 16 '18 at 11:01
@DenisRubashkin we run a full daily backup, and differential backups every 20 mins. The previous days diffs are deleted after the full backup is taken, and we only spotted this issue this morning, so I don't know whether yesterdays differential backups were interrupted. I can see that the backups from today are running fine though. The backups are run as maintenance plans
– Jordan Wallwork
Nov 16 '18 at 11:05
"This happened about 5 months ago" --......? Talk to your DBA....They won't bite.....much..... Please clarify: it took 5 months for the fact that log shipping was broken to be noticed?
– Mitch Wheat
Nov 16 '18 at 10:49
"This happened about 5 months ago" --......? Talk to your DBA....They won't bite.....much..... Please clarify: it took 5 months for the fact that log shipping was broken to be noticed?
– Mitch Wheat
Nov 16 '18 at 10:49
docs.microsoft.com/en-us/sql/database-engine/log-shipping/…
– Mitch Wheat
Nov 16 '18 at 10:55
docs.microsoft.com/en-us/sql/database-engine/log-shipping/…
– Mitch Wheat
Nov 16 '18 at 10:55
1
1
How are your backups performed? Is it a job or a maintenance plan or something? It seems as if there is some logic like if a backup log is failed then switch to the simple mode and perform the differential one. Check it.
– Denis Rubashkin
Nov 16 '18 at 10:56
How are your backups performed? Is it a job or a maintenance plan or something? It seems as if there is some logic like if a backup log is failed then switch to the simple mode and perform the differential one. Check it.
– Denis Rubashkin
Nov 16 '18 at 10:56
@MitchWheat about 5 months ago the recovery mode switched and log shipping failed. I set it back to Full recovery and set log shipping back up from scratch. Unfortunately we're a small operation and aside from a couple of other devs that help me with building our product I'm the dogsbody jack-of-all-trades who has stumbled into the role architect/sysadmin/dba etc!
– Jordan Wallwork
Nov 16 '18 at 11:01
@MitchWheat about 5 months ago the recovery mode switched and log shipping failed. I set it back to Full recovery and set log shipping back up from scratch. Unfortunately we're a small operation and aside from a couple of other devs that help me with building our product I'm the dogsbody jack-of-all-trades who has stumbled into the role architect/sysadmin/dba etc!
– Jordan Wallwork
Nov 16 '18 at 11:01
@DenisRubashkin we run a full daily backup, and differential backups every 20 mins. The previous days diffs are deleted after the full backup is taken, and we only spotted this issue this morning, so I don't know whether yesterdays differential backups were interrupted. I can see that the backups from today are running fine though. The backups are run as maintenance plans
– Jordan Wallwork
Nov 16 '18 at 11:05
@DenisRubashkin we run a full daily backup, and differential backups every 20 mins. The previous days diffs are deleted after the full backup is taken, and we only spotted this issue this morning, so I don't know whether yesterdays differential backups were interrupted. I can see that the backups from today are running fine though. The backups are run as maintenance plans
– Jordan Wallwork
Nov 16 '18 at 11:05
add a comment |
1 Answer
1
active
oldest
votes
Discovered the reason that this is happening is because of a flag on our db migration script runner (RoundhousE) which resets the recovery mode to Simple. I hadn't been aware of any deployments that day featuring db migrations (these are fairly uncommon on our system), and I have no idea why the /simple flag was configured on the deployments as they were setup before my involvement. Removing the flag has sorted the problem, and seems to have had no negative repercussions!
answered Nov 20 '18 at 14:54
Jordan WallworkJordan Wallwork
1,91611943
add a comment |
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53335959%2fsql-server-2016-reset-recovery-mode-from-full-to-simple%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
Discovered the reason that this is happening is because of a flag on our db migration script runner (RoundhousE) which resets the recovery mode to Simple. I hadn't been aware of any deployments that day featuring db migrations (these are fairly uncommon on our system), and I have no idea why the /simple flag was configured on the deployments as they were setup before my involvement. Removing the flag has sorted the problem, and seems to have had no negative repercussions!
answered Nov 20 '18 at 14:54
Jordan WallworkJordan Wallwork
1,91611943
add a comment |
Discovered the reason that this is happening is because of a flag on our db migration script runner (RoundhousE) which resets the recovery mode to Simple. I hadn't been aware of any deployments that day featuring db migrations (these are fairly uncommon on our system), and I have no idea why the /simple flag was configured on the deployments as they were setup before my involvement. Removing the flag has sorted the problem, and seems to have had no negative repercussions!
answered Nov 20 '18 at 14:54
Jordan WallworkJordan Wallwork
1,91611943
add a comment |
Discovered the reason that this is happening is because of a flag on our db migration script runner (RoundhousE) which resets the recovery mode to Simple. I hadn't been aware of any deployments that day featuring db migrations (these are fairly uncommon on our system), and I have no idea why the /simple flag was configured on the deployments as they were setup before my involvement. Removing the flag has sorted the problem, and seems to have had no negative repercussions!
answered Nov 20 '18 at 14:54
Jordan WallworkJordan Wallwork
1,91611943
Discovered the reason that this is happening is because of a flag on our db migration script runner (RoundhousE) which resets the recovery mode to Simple. I hadn't been aware of any deployments that day featuring db migrations (these are fairly uncommon on our system), and I have no idea why the /simple flag was configured on the deployments as they were setup before my involvement. Removing the flag has sorted the problem, and seems to have had no negative repercussions!
answered Nov 20 '18 at 14:54
Jordan WallworkJordan Wallwork
1,91611943
answered Nov 20 '18 at 14:54
Jordan WallworkJordan Wallwork
1,91611943
answered Nov 20 '18 at 14:54
Jordan WallworkJordan Wallwork
1,91611943
answered Nov 20 '18 at 14:54
Jordan WallworkJordan Wallwork
1,91611943
1,91611943
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53335959%2fsql-server-2016-reset-recovery-mode-from-full-to-simple%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
"This happened about 5 months ago" --......? Talk to your DBA....They won't bite.....much..... Please clarify: it took 5 months for the fact that log shipping was broken to be noticed?
– Mitch Wheat
Nov 16 '18 at 10:49
docs.microsoft.com/en-us/sql/database-engine/log-shipping/…
– Mitch Wheat
Nov 16 '18 at 10:55
1
How are your backups performed? Is it a job or a maintenance plan or something? It seems as if there is some logic like if a backup log is failed then switch to the simple mode and perform the differential one. Check it.
– Denis Rubashkin
Nov 16 '18 at 10:56
@MitchWheat about 5 months ago the recovery mode switched and log shipping failed. I set it back to Full recovery and set log shipping back up from scratch. Unfortunately we're a small operation and aside from a couple of other devs that help me with building our product I'm the dogsbody jack-of-all-trades who has stumbled into the role architect/sysadmin/dba etc!
– Jordan Wallwork
Nov 16 '18 at 11:01
@DenisRubashkin we run a full daily backup, and differential backups every 20 mins. The previous days diffs are deleted after the full backup is taken, and we only spotted this issue this morning, so I don't know whether yesterdays differential backups were interrupted. I can see that the backups from today are running fine though. The backups are run as maintenance plans
– Jordan Wallwork
Nov 16 '18 at 11:05