Strange blocking on readable secondary after rebootAlwaysOn Availability Groups Delayed...
Strange Sign on Lab Door
Would a National Army of mercenaries be a feasible idea?
Why exactly do action photographers need high fps burst cameras?
How to avoid being sexist when trying to employ someone to function in a very sexist environment?
How to prevent cleaner from hanging my lock screen in Ubuntu 16.04
Why do members of Congress in committee hearings ask witnesses the same question multiple times?
Injecting creativity into a cookbook
Difference between `vector<int> v;` and `vector<int> v = vector<int>();`
In Linux what happens if 1000 files in a directory are moved to another location while another 300 files were added to the source directory?
Caruana vs Carlsen game 10 (WCC) why not 18...Nxb6?
Why zero tolerance on nudity in space?
Can a hotel cancel a confirmed reservation?
Pandas: How to group by a value in column when there is list in one of the columns
Would these multi-classing house rules cause unintended problems?
Strange blocking on readable secondary after reboot
It took me a lot of time to make this, pls like. (YouTube Comments #1)
Avoiding morning and evening handshakes
Can I write a book of my D&D game?
Broken patches on a road
Can we use the stored gravitational potential energy of a building to produce power?
A starship is travelling at 0.9c and collides with a small rock. Will it leave a clean hole through, or will more happen?
What is the most triangles you can make from a capital "H" and 3 straight lines?
Why would the Pakistan airspace closure cancel flights not headed to Pakistan itself?
How do you funnel food off a cutting board?
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 1 hour ago
jadarnel27
5,98311938
asked 2 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 1 hour ago
jadarnel27
5,98311938
asked 2 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
52 mins 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 1 hour ago
jadarnel27
5,98311938
asked 2 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 1 hour ago
jadarnel27
5,98311938
asked 2 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 1 hour ago
jadarnel27
5,98311938
asked 2 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 1 hour ago
jadarnel27
5,98311938
edited 1 hour ago
jadarnel27
5,98311938
edited 1 hour ago
jadarnel27
5,98311938
5,98311938
asked 2 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 2 hours ago
Jogchum RoodaJogchum Rooda
261
asked 2 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
52 mins 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
52 mins 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
52 mins 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
52 mins 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 1 hour ago
jadarnel27jadarnel27
5,98311938
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 1 hour ago
jadarnel27jadarnel27
5,98311938
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 1 hour ago
jadarnel27jadarnel27
5,98311938
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 1 hour ago
jadarnel27jadarnel27
5,98311938
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 1 hour ago
jadarnel27jadarnel27
5,98311938
answered 1 hour ago
jadarnel27jadarnel27
5,98311938
answered 1 hour ago
jadarnel27jadarnel27
5,98311938
answered 1 hour ago
jadarnel27jadarnel27
5,98311938
5,98311938
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
52 mins ago