none
Switching from Asynchronous to Synchronous mirroring and Blocking;

    Question

  • Hi I have a sql 2008 Enterprise Edition sp3 cu2 server with 3 databases mirrored. When switching mirroring safety level to FULL,

    notice threads hung and blocking other threads with DBMIRROR_DBM_EVENT,also the send queue increases on principal.

    Just wanted to why the blocking/hung thread seem to happen sporadically and not every time?.

    Even when workload is very minimal ,switching to Safety FULL leaves some spids hung with that wait type.

    Thanks

    Wednesday, February 15, 2012 9:02 PM

Answers

  • DBMIRROR_DBM_EVENT is the wait type you get when a transaction waits for the (sync) mirror partner to send back the commit. This is quite normal with sync mirorrs, although I have never seen this during the switch from async to sync.

    To troubleshoot the wait you should have a look at the mirror partner. On low load systems I would bet that you see something like a WRITELOG wait over there or an ASYNC_NETWORK_IO. On very high load system the thing is a little different, but that's another story...

    • Marked as answer by SQL_Jay Thursday, February 16, 2012 2:20 PM
    Thursday, February 16, 2012 6:54 AM
  • Hi,

    See the following bug report, it might match with your problem.

    Some sessions are suspended after Mirroring Safety is set back to Full

    JN.


    Jean-Nicolas BERGER
    http://blog.sqlserver.fr

    • Marked as answer by SQL_Jay Friday, July 26, 2013 8:57 PM
    Friday, January 04, 2013 1:17 AM

All replies

  • DBMIRROR_DBM_EVENT is the wait type you get when a transaction waits for the (sync) mirror partner to send back the commit. This is quite normal with sync mirorrs, although I have never seen this during the switch from async to sync.

    To troubleshoot the wait you should have a look at the mirror partner. On low load systems I would bet that you see something like a WRITELOG wait over there or an ASYNC_NETWORK_IO. On very high load system the thing is a little different, but that's another story...

    • Marked as answer by SQL_Jay Thursday, February 16, 2012 2:20 PM
    Thursday, February 16, 2012 6:54 AM
  • Thanks will analyze waits around that time.

    Thursday, February 16, 2012 2:20 PM
  • Hi,

    See the following bug report, it might match with your problem.

    Some sessions are suspended after Mirroring Safety is set back to Full

    JN.


    Jean-Nicolas BERGER
    http://blog.sqlserver.fr

    • Marked as answer by SQL_Jay Friday, July 26, 2013 8:57 PM
    Friday, January 04, 2013 1:17 AM