SQL Server blocking caused by database mirroring wait type DBMIRROR_DBM_EVENT

I was looking at a problem this morning about long blocking chains caused by the wait type DBMIRROR_DBM_EVENT, and it makes an interesting case study about database mirroring, blocking, undocumented wait types and general troubleshooting techniques for database mirroring. The information following relates ONLY to SQL 2005, SQL 2008 and SQL 2008 R2. However since…

0

SQL Server Database Mirroring Pre-requisites and guidelines

I noticed that we have posted a really good summary of the above points through the new fast publish system for KBs. (Note the disclaimer at the bottom which is not normally shown on full KBs). There are some really good points here to consider about setting up and configuring a mirroring environment. Things to…

0

Sample Application to test database mirroring failover

Here’s the source for a tiny C# app I wrote to test database mirroring failover at the application level, including a sample database mirroring connection string. It assumes that you have already set up a mirroring partnership. It doesn’t matter whether the partnership includes a witness or has auto or manual failover. I’ll let you…

1

Database Mirroring Error 1418 Troubleshooter

I’ve been posting in the MSDN and TECHNET mirroring forums in the last few days and I’ve noticed a lot of occurrences of the 1418 error which I have referred to briefly in this blog before. Therefore I thought I’d post a 1418 specific troubleshooter for reference. The problem we’re trying to solve is that…

3

How to configure SQL Server database mirroring between 2 instances on a standalone server

There are lots of troubleshooting articles about how to diagnose connectivity problems when setting up database mirroring, especially errors such as these: Msg 1418, Level 16, State 1, Line 2 The server network address “tcp://mirrortest.com:5023” can not be reached or does not exist. Check the network address name and that the ports for the local…

1

Database Mirroring Monitor can cause large tempdb growth in SQL Server 2005 - changed behavior in SQL Server 2008

On extremely rare occasions in SQL Server 2005, when the following conditions are true:   1.       You have a database configured for database mirroring 2.       You have replication configured on the same database 3.       You are running the database mirroring monitor 4.       The SQL Agent job created by the database mirroring monitor is enabled  …

3

1413 error when starting Database Mirroring – How many virtual log files is too many?

Recently in Cumulative update package 6 for SQL Server 2005 Service Pack 2 we released a fix for a problem where you receive a 1413 error when starting database mirroring. The error that you get in the looks like this: Error: 1413, Severity: 16, State: 1. Communications to the remote server instance ‘TCP://ComputerName:PortNumber’ failed before…

1