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

Answer 1

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...

Answer 2

Thanks will analyze waits around that time.

Answer 3

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