Ggthjy

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

5

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

share|improve this question

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 | 

5

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

share|improve this question

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

share|improve this question

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.

share|improve this question

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.

share|improve this question

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

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

1 Answer
1

active

oldest

votes

3

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.

share|improve this answer

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.

draft saved

draft discarded

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

Name

Email

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

Name

Email

Required, but never shown

3

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.

share|improve this answer

answered 14 hours ago

jadarnel27jadarnel27

6,01811938

add a comment | 

3

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.

share|improve this answer

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.

share|improve this answer

answered 14 hours ago

jadarnel27jadarnel27

6,01811938

share|improve this answer

answered 14 hours ago

jadarnel27jadarnel27

6,01811938

answered 14 hours ago

jadarnel27jadarnel27

6,01811938

answered 14 hours ago

jadarnel27jadarnel27

6,01811938