0

I have an issue with Always On Availability Groups In SQL Server 2012.

I installed a replica secondary SQLNODE3. It was fine and everything retrievable form tables but suddenly I found I could not read anything from the database. In Activity Monitor, I see the query is suspended and the wait type is:

HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING

I wait for the updating transition for more than day but nothing happened.

I restarted the replica server and created the AG from scratch but still cannot read data.

How can I read from the replica and avoid this wait type?

2

This sounds like you might have a long running transaction on the primary replica. Run this query to find any transactions that have been running longer than 10 mins:

Select  B.session_id,
    A.transaction_id,
    C.transaction_begin_time,
    DATEDIFF(Second,C.transaction_begin_time,getdate()) TimeTaken_In_Seconds,
    B.HOST_NAME,
    B.program_name,
    B.login_name,b.login_time as UserLoginTime 
    from sys.dm_tran_session_transactions A Join sys.dm_exec_sessions B On A.session_id=B.session_id 
    Join sys.dm_tran_active_transactions C On A.transaction_id = C.transaction_id
    WHERE DATEDIFF(Second,C.transaction_begin_time,getdate())  > 600

Once you find the transaction, either complete it, or kill it.

0

Seems like an open transaction (waiting to commit or rollback) could cause the secondary replica to stop answering queries. To find such a transaction on the primary, another alternative could be:

DBCC Opentran

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.