Share via

SQL AG not in sync .. open transaction with spid ending with s

shanmugasundaram rageswari 96 Reputation points
2021-06-25T21:45:01.11+00:00

we have one primary and 3 secondary nodes (first 2 is sync mode and last 2 in async mode). please see the attachment and suggest what can be done to fix lag issue
we are seeing the lag in the last node (10+ hrs)

109522-ag-lag.png
Oldest active transaction:
SPID (server process ID): 201s
UID (user ID) : -1
Name : ReorderPages
LSN : (1192075:655776:1)
Start time : Jun 25 2021 2:03:00:440AM
SID : 0x010500000000000515000000ff4e7d090240be3d240db112adad0100

Replicated Transaction Information:
Oldest distributed LSN : (1191145:913922:72)
Oldest non-distributed LSN : (1191163:100428:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Completion time: 2021-06-25T14:30:06.0476015-07:00

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,363 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CarrinWu-MSFT 6,866 Reputation points
    2021-06-29T02:21:03.087+00:00

    Hi @shanmugasundaram rageswari ,

    Asynchronous-commit secondary databases always remain unsynchronized and are likely to lag somewhat behind the corresponding primary databases. Typically the gap between an asynchronous-commit secondary database and the corresponding primary database is small. But the gap can become substantial if the server hosting the secondary replica is over loaded or the network is slow. Please refer to Asynchronous-Commit Availability Mode and this thread to get more information.

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments