Cannot read from secondary availability group

Question

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?

Answer 1

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.

Answer 2

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