Strange blocking on readable secondary after rebootAlwaysOn Availability Groups Delayed...
Word or phrase for showing great skill at something without formal training in it
Why is working on the same position for more than 15 years not a red flag?
How to deal with an incendiary email that was recalled
What does Cypher mean when he says Neo is "gonna pop"?
If I delete my router's history can my ISP still provide it to my parents?
How to tag distinct options/entities without giving any an implicit priority or suggested order?
Difference between two quite-similar Terminal commands
Does fast page mode apply to ROM?
Where are a monster’s hit dice found in the stat block?
Why doesn't "auto ch = unsigned char{'p'}" compile under C++ 17?
Checking for the existence of multiple directories
How do I say "Brexit" in Latin?
How to prevent cleaner from hanging my lock screen in Ubuntu 16.04
How should I handle players who ignore the session zero agreement?
Explain the objections to these measures against human trafficking
Why do members of Congress in committee hearings ask witnesses the same question multiple times?
Why do neural networks need so many training examples to perform?
insert EOF statement before the last line of file
Can a person refuse a presidential pardon?
Can a dragon be stuck looking like a human?
Is there some relative to Dutch word "kijken" in German?
Eww, those bytes are gross
Is there any differences between "Gucken" and "Schauen"?
Should I write a companion book/blog?
Strange blocking on readable secondary after reboot
AlwaysOn Availability Groups Delayed ReplicaApplicationIntent=ReadOnly Traffic when no Readable Secondary AvailableSQL Server AlwaysOn database stuck in Not Synchronizing / In Recovery mode after upgrading. Error: Cannot open database '…' version 782Cannot read from secondary availability groupDatabase not able to resume data movementAlwaysOn ClusterAlways On Availability Group ApplicationIntent=ReadOnly Not routing to SecondaryQuestions on Availability Group Readable SecondaryThe backup is failing after few minutes/percent in two node SQL Server AlwaysOnSQL Server Distributed Availability Group databases not syncing after a server reboot
We are running an SQL Server 2014 Availability Group with 3 replicas, one synchronous (SQL2 for this matter) and one asynchronous secondary replica. We also configured read-only routing to the synchronous secondary replica.
Last night SQL2 rebooted from automatic windows update installation. The server went back online, SQL Server service started (delayed start) and the database went into recovery. After a while the event viewer showed the database integrity check succeeded and the database was ready for use.
The database showed synchronized state in SQL Management studio. The AG state was healthy, but no queries were getting results from the database.
The queries were blocked by the wait type: HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING.
Sometimes the wait type changed to ‘lck_m_s’ wait type and the blocked by a pid that was a process performing a DB Startup command. I know this has to do with the Fast Recovery option that comes with SQL Server Enterprise, but I don’t understand why a simple select was blocked forever.
The main question is: How can SQL Server show the AG database is healthy but actually it isn’t? Do you recognize this problem?
To fix this, we removed the secondary from the AG and joined the database back again to the AG and now everything is working again.
sql-server sql-server-2014 availability-groups blocking wait-types
edited 14 hours ago
jadarnel27
6,01811938
asked 16 hours ago
Jogchum RoodaJogchum Rooda
261
New contributor
Jogchum Rooda is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
We are running an SQL Server 2014 Availability Group with 3 replicas, one synchronous (SQL2 for this matter) and one asynchronous secondary replica. We also configured read-only routing to the synchronous secondary replica.
Last night SQL2 rebooted from automatic windows update installation. The server went back online, SQL Server service started (delayed start) and the database went into recovery. After a while the event viewer showed the database integrity check succeeded and the database was ready for use.
The database showed synchronized state in SQL Management studio. The AG state was healthy, but no queries were getting results from the database.
The queries were blocked by the wait type: HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING.
Sometimes the wait type changed to ‘lck_m_s’ wait type and the blocked by a pid that was a process performing a DB Startup command. I know this has to do with the Fast Recovery option that comes with SQL Server Enterprise, but I don’t understand why a simple select was blocked forever.
The main question is: How can SQL Server show the AG database is healthy but actually it isn’t? Do you recognize this problem?
To fix this, we removed the secondary from the AG and joined the database back again to the AG and now everything is working again.
sql-server sql-server-2014 availability-groups blocking wait-types
edited 14 hours ago
jadarnel27
6,01811938
asked 16 hours ago
Jogchum RoodaJogchum Rooda
261
New contributor
Jogchum Rooda is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Posting your sql server event log at the time of the secondary coming back online could be of help to see what the issue was.
– Randi Vertongen
14 hours ago
add a comment |
We are running an SQL Server 2014 Availability Group with 3 replicas, one synchronous (SQL2 for this matter) and one asynchronous secondary replica. We also configured read-only routing to the synchronous secondary replica.
Last night SQL2 rebooted from automatic windows update installation. The server went back online, SQL Server service started (delayed start) and the database went into recovery. After a while the event viewer showed the database integrity check succeeded and the database was ready for use.
The database showed synchronized state in SQL Management studio. The AG state was healthy, but no queries were getting results from the database.
The queries were blocked by the wait type: HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING.
Sometimes the wait type changed to ‘lck_m_s’ wait type and the blocked by a pid that was a process performing a DB Startup command. I know this has to do with the Fast Recovery option that comes with SQL Server Enterprise, but I don’t understand why a simple select was blocked forever.
The main question is: How can SQL Server show the AG database is healthy but actually it isn’t? Do you recognize this problem?
To fix this, we removed the secondary from the AG and joined the database back again to the AG and now everything is working again.
sql-server sql-server-2014 availability-groups blocking wait-types
edited 14 hours ago
jadarnel27
6,01811938
asked 16 hours ago
Jogchum RoodaJogchum Rooda
261
New contributor
Jogchum Rooda is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
We are running an SQL Server 2014 Availability Group with 3 replicas, one synchronous (SQL2 for this matter) and one asynchronous secondary replica. We also configured read-only routing to the synchronous secondary replica.
Last night SQL2 rebooted from automatic windows update installation. The server went back online, SQL Server service started (delayed start) and the database went into recovery. After a while the event viewer showed the database integrity check succeeded and the database was ready for use.
The database showed synchronized state in SQL Management studio. The AG state was healthy, but no queries were getting results from the database.
The queries were blocked by the wait type: HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING.
Sometimes the wait type changed to ‘lck_m_s’ wait type and the blocked by a pid that was a process performing a DB Startup command. I know this has to do with the Fast Recovery option that comes with SQL Server Enterprise, but I don’t understand why a simple select was blocked forever.
The main question is: How can SQL Server show the AG database is healthy but actually it isn’t? Do you recognize this problem?
To fix this, we removed the secondary from the AG and joined the database back again to the AG and now everything is working again.
sql-server sql-server-2014 availability-groups blocking wait-types
sql-server sql-server-2014 availability-groups blocking wait-types
edited 14 hours ago
jadarnel27
6,01811938
asked 16 hours ago
Jogchum RoodaJogchum Rooda
261
New contributor
Jogchum Rooda is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 14 hours ago
jadarnel27
6,01811938
asked 16 hours ago
Jogchum RoodaJogchum Rooda
261
New contributor
Jogchum Rooda is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 14 hours ago
jadarnel27
6,01811938
edited 14 hours ago
jadarnel27
6,01811938
edited 14 hours ago
jadarnel27
6,01811938
6,01811938
asked 16 hours ago
Jogchum RoodaJogchum Rooda
261
New contributor
Jogchum Rooda is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 16 hours ago
Jogchum RoodaJogchum Rooda
261
asked 16 hours ago
Jogchum RoodaJogchum Rooda
261
261
New contributor
Jogchum Rooda is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Jogchum Rooda is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Jogchum Rooda is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Posting your sql server event log at the time of the secondary coming back online could be of help to see what the issue was.
– Randi Vertongen
14 hours ago
add a comment |
Posting your sql server event log at the time of the secondary coming back online could be of help to see what the issue was.
– Randi Vertongen
14 hours ago
Posting your sql server event log at the time of the secondary coming back online could be of help to see what the issue was.
– Randi Vertongen
14 hours ago
Posting your sql server event log at the time of the secondary coming back online could be of help to see what the issue was.
– Randi Vertongen
14 hours ago
add a comment |
1 Answer
1
active
oldest
votes
It sounds like you experienced the behavior that's described in this post on the PFE blog:
AlwaysOn Availability Groups unable to query against readable secondary replica database: Wait Type HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
Essentially, there happened to be a long-running transaction on the primary when the secondary was made readable, and thus queries will be blocked in the secondary until the snapshot-related row versions are available. I imagine removing and re-adding the database was coincidental with the long-running transaction finally completing.
So this behavior, as described in that blog post, is by design.
However, if there was not a long-running transaction, then this could be a bug. There is a comment on that blog that indicates others have had this problem:
I am facing the issue after Secondary SQL Server reboot after OS patching. Do not see any open transactions in primary prior to the reboot. There are two databases in the AG having this issue. And we have been waiting for more than 15 hours but still readable replica is not able to process any select query for those databases.
If you are able to repeat the behavior, it would be good to report it on the feedback site and / or engage Microsoft support if you have a support agreement.
answered 14 hours ago
jadarnel27jadarnel27
6,01811938
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
});
}
});
Jogchum Rooda is a new contributor. Be nice, and check out our Code of Conduct.
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%2f231038%2fstrange-blocking-on-readable-secondary-after-reboot%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
It sounds like you experienced the behavior that's described in this post on the PFE blog:
AlwaysOn Availability Groups unable to query against readable secondary replica database: Wait Type HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
Essentially, there happened to be a long-running transaction on the primary when the secondary was made readable, and thus queries will be blocked in the secondary until the snapshot-related row versions are available. I imagine removing and re-adding the database was coincidental with the long-running transaction finally completing.
So this behavior, as described in that blog post, is by design.
However, if there was not a long-running transaction, then this could be a bug. There is a comment on that blog that indicates others have had this problem:
I am facing the issue after Secondary SQL Server reboot after OS patching. Do not see any open transactions in primary prior to the reboot. There are two databases in the AG having this issue. And we have been waiting for more than 15 hours but still readable replica is not able to process any select query for those databases.
If you are able to repeat the behavior, it would be good to report it on the feedback site and / or engage Microsoft support if you have a support agreement.
answered 14 hours ago
jadarnel27jadarnel27
6,01811938
add a comment |
It sounds like you experienced the behavior that's described in this post on the PFE blog:
AlwaysOn Availability Groups unable to query against readable secondary replica database: Wait Type HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
Essentially, there happened to be a long-running transaction on the primary when the secondary was made readable, and thus queries will be blocked in the secondary until the snapshot-related row versions are available. I imagine removing and re-adding the database was coincidental with the long-running transaction finally completing.
So this behavior, as described in that blog post, is by design.
However, if there was not a long-running transaction, then this could be a bug. There is a comment on that blog that indicates others have had this problem:
I am facing the issue after Secondary SQL Server reboot after OS patching. Do not see any open transactions in primary prior to the reboot. There are two databases in the AG having this issue. And we have been waiting for more than 15 hours but still readable replica is not able to process any select query for those databases.
If you are able to repeat the behavior, it would be good to report it on the feedback site and / or engage Microsoft support if you have a support agreement.
answered 14 hours ago
jadarnel27jadarnel27
6,01811938
add a comment |
It sounds like you experienced the behavior that's described in this post on the PFE blog:
AlwaysOn Availability Groups unable to query against readable secondary replica database: Wait Type HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
Essentially, there happened to be a long-running transaction on the primary when the secondary was made readable, and thus queries will be blocked in the secondary until the snapshot-related row versions are available. I imagine removing and re-adding the database was coincidental with the long-running transaction finally completing.
So this behavior, as described in that blog post, is by design.
However, if there was not a long-running transaction, then this could be a bug. There is a comment on that blog that indicates others have had this problem:
I am facing the issue after Secondary SQL Server reboot after OS patching. Do not see any open transactions in primary prior to the reboot. There are two databases in the AG having this issue. And we have been waiting for more than 15 hours but still readable replica is not able to process any select query for those databases.
If you are able to repeat the behavior, it would be good to report it on the feedback site and / or engage Microsoft support if you have a support agreement.
answered 14 hours ago
jadarnel27jadarnel27
6,01811938
It sounds like you experienced the behavior that's described in this post on the PFE blog:
AlwaysOn Availability Groups unable to query against readable secondary replica database: Wait Type HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
Essentially, there happened to be a long-running transaction on the primary when the secondary was made readable, and thus queries will be blocked in the secondary until the snapshot-related row versions are available. I imagine removing and re-adding the database was coincidental with the long-running transaction finally completing.
So this behavior, as described in that blog post, is by design.
However, if there was not a long-running transaction, then this could be a bug. There is a comment on that blog that indicates others have had this problem:
I am facing the issue after Secondary SQL Server reboot after OS patching. Do not see any open transactions in primary prior to the reboot. There are two databases in the AG having this issue. And we have been waiting for more than 15 hours but still readable replica is not able to process any select query for those databases.
If you are able to repeat the behavior, it would be good to report it on the feedback site and / or engage Microsoft support if you have a support agreement.
answered 14 hours ago
jadarnel27jadarnel27
6,01811938
answered 14 hours ago
jadarnel27jadarnel27
6,01811938
answered 14 hours ago
jadarnel27jadarnel27
6,01811938
answered 14 hours ago
jadarnel27jadarnel27
6,01811938
6,01811938
add a comment |
add a comment |
Jogchum Rooda is a new contributor. Be nice, and check out our Code of Conduct.
Jogchum Rooda is a new contributor. Be nice, and check out our Code of Conduct.
Jogchum Rooda is a new contributor. Be nice, and check out our Code of Conduct.
Jogchum Rooda is a new contributor. Be nice, and check out our Code of Conduct.
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%2f231038%2fstrange-blocking-on-readable-secondary-after-reboot%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
Posting your sql server event log at the time of the secondary coming back online could be of help to see what the issue was.
– Randi Vertongen
14 hours ago