Environment: 2 Servers SQL 2008 R2 RTM Standard Edition, Windows Server 2008 R2 SP1 standard edition. Both servers using the same domain service account for SQL with sysadmin access and local Administrator group access on both servers.
4 databases are successfully mirrored without issue, all database owners are sa.
When we attempt to setup mirroring on the 5th database it successfully starts and then within a minute goes to a (Principal disconnected) state on Primary and stays at (Mirror, Syncronizing / Restoring) on the mirror. The Mirroring monitor just shows "Disconnected" from the beginning of the event for the fifth database.
I have run a SQL Trace while setting up mirroring and the only thing in them is "Attention" with the service account and host name, no other details.
The Windows Application logs on both Primary and Mirror server show SQL Server event ID 1438. We are not using a witness and have never attempted to set one up in this environment.
Any suggestions on what to check would be appreciated
Best Answer
You may not have enough available threads. See how many are available with sys.dm_os_sys_info. See how many are being used with sys.dm_os_schedulers (sum of rows in column current_workers_count)
Database mirroring has the following requirements for worker threads Principal server: 1 global thread and 2 threads per each of the mirrored databases. Mirror server: 1 global thread, 2 threads per mirrored databases and one additional thread for each mirrored database for every 4 processor cores. On a 4 CPU server it requires 1 global thread+ (2+ 1) threads /mirrored db. On a 6-8 CPU server it requires 1 global thread +(2+2) threads /mirrored db. https://support.microsoft.com/en-us/kb/2001270
You may not have enough RAM. You may not have enough network bandwidth. Your disk subsystem may be overwhelmed. Your servers may have too heavy of a load to support another mirrored database.