none
AlwaysOn

    Question

  • Can anybody explain why a long running read-query on secondary replica can block the ghost records cleanup on the primary replica? Thanks.
    Thursday, April 10, 2014 9:08 PM

Answers

  • Hello,

    Because read operations are mapped to snapshot isolation transaction level, the cleanup of ghost records on the primary replica can be blocked by transactions on one or more secondary replicas. The ghost record cleanup task will automatically clean up the ghost records on the primary replica when they are no longer needed by any secondary replica.  This is similar to what is done when you run transaction(s) on the primary replica. In the extreme case on the secondary database, you will need to kill a long running read-query that is blocking the ghost cleanup. Note, the ghost clean can be blocked if the secondary replica gets disconnected or when data movement is suspended on the secondary database. This state also prevents log truncation, so if this state persists, we recommend that you remove this secondary database from the availability group.

    Reference: http://technet.microsoft.com/en-us/library/ff878253.aspx

    Short and sweet.


    Sean Gallardy | Blog | Twitter

    Thursday, April 10, 2014 10:20 PM
    Answerer
  • http://blogs.msdn.com/b/psssql/archive/2013/04/22/how-it-works-always-on-when-is-my-secondary-failover-ready.aspx

    http://msdn.microsoft.com/en-us/library/ff878253(v=sql.120).aspx


    Raju Rasagounder MSSQL DBA

    Friday, April 11, 2014 12:21 AM

All replies

  • Hello,

    Because read operations are mapped to snapshot isolation transaction level, the cleanup of ghost records on the primary replica can be blocked by transactions on one or more secondary replicas. The ghost record cleanup task will automatically clean up the ghost records on the primary replica when they are no longer needed by any secondary replica.  This is similar to what is done when you run transaction(s) on the primary replica. In the extreme case on the secondary database, you will need to kill a long running read-query that is blocking the ghost cleanup. Note, the ghost clean can be blocked if the secondary replica gets disconnected or when data movement is suspended on the secondary database. This state also prevents log truncation, so if this state persists, we recommend that you remove this secondary database from the availability group.

    Reference: http://technet.microsoft.com/en-us/library/ff878253.aspx

    Short and sweet.


    Sean Gallardy | Blog | Twitter

    Thursday, April 10, 2014 10:20 PM
    Answerer
  • http://blogs.msdn.com/b/psssql/archive/2013/04/22/how-it-works-always-on-when-is-my-secondary-failover-ready.aspx

    http://msdn.microsoft.com/en-us/library/ff878253(v=sql.120).aspx


    Raju Rasagounder MSSQL DBA

    Friday, April 11, 2014 12:21 AM