Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We've got a very simple mirroring setup here: just a principal DB and mirror DB, no witness, running in high-performance mode with SET PARTNER SAFETY OFF.

This problem has happened to us twice now: whenever something goes wrong on the mirror server, it causes the principal DB to become unavailable. We definitely don't want that behavior!

Our mirror server is located off-site at a hosting company, and they periodically run backups and maintenance which cause the mirror server to go offline. But I don't ever want the principal to go down just because of a problem with the mirror. Is there some other setting in SQL Server that I'm not seeing, besides SET PARTNER SAFETY OFF?

We're running SQL Server 2012, Enterprise with SP1.

I'll include the log entries here. Appreciate any tips or help you can provide!

.

Log from the Principal server


2014-01-08 18:14:51.43 spid36s     Error: 1479, Severity: 16, State: 1.
2014-01-08 18:14:51.43 spid36s     The mirroring connection to "TCP://mirrordb.mydomain.com:5022" has timed out for database "PROD" after 10 seconds without a response.  Check the service and network connections.
2014-01-08 18:14:51.43 spid36s     Database mirroring is inactive for database 'PROD'. This is an informational message only. No user action is required.
2014-01-08 18:19:58.51 spid36s     Error: 1479, Severity: 16, State: 1.
2014-01-08 18:19:58.51 spid36s     The mirroring connection to "TCP://mirrordb.mydomain.com:5022" has timed out for database "PROD" after 10 seconds without a response.  Check the service and network connections.
2014-01-08 18:19:59.55 Logon       Error: 18456, Severity: 14, State: 38.
2014-01-08 18:19:59.55 Logon       Login failed for user 'WWW\prod'. Reason: Failed to open the explicitly specified database 'PROD'. [CLIENT: ]
2014-01-08 18:19:59.83 Logon       Error: 18456, Severity: 14, State: 38.
2014-01-08 18:19:59.83 Logon       Login failed for user 'WWW\prod'. Reason: Failed to open the explicitly specified database 'PROD'. [CLIENT: ]
2014-01-08 18:20:02.17 spid21s     Starting up database 'PROD'.
2014-01-08 18:20:03.15 Logon       Error: 18456, Severity: 14, State: 38.
2014-01-08 18:20:03.15 Logon       Login failed for user 'WWW\prod'. Reason: Failed to open the explicitly specified database 'PROD'. [CLIENT: ]
2014-01-08 18:20:03.51 Logon       Error: 18456, Severity: 14, State: 38.
2014-01-08 18:20:03.51 Logon       Login failed for user 'WWW\prod'. Reason: Failed to open the explicitly specified database 'PROD'. [CLIENT: ]
2014-01-08 18:20:04.17 Logon       Error: 18456, Severity: 14, State: 38.
2014-01-08 18:20:04.17 Logon       Login failed for user 'WWW\prod'. Reason: Failed to open the explicitly specified database 'PROD'. [CLIENT: ]
2014-01-08 18:20:31.21 Logon       Error: 18456, Severity: 14, State: 38.
2014-01-08 18:20:31.21 Logon       Login failed for user 'WWW\prod'. Reason: Failed to open the explicitly specified database 'PROD'. [CLIENT: ]
2014-01-08 18:20:32.63 Logon       Error: 18456, Severity: 14, State: 38.
2014-01-08 18:20:32.63 Logon       Login failed for user 'WWW\prod'. Reason: Failed to open the explicitly specified database 'PROD'. [CLIENT: ]
2014-01-08 18:20:33.52 spid36s     Error: 1479, Severity: 16, State: 1.
2014-01-08 18:20:33.52 spid36s     The mirroring connection to "TCP://mirrordb.mydomain.com:5022" has timed out for database "PROD" after 10 seconds without a response.  Check the service and network connections.
2014-01-08 18:20:38.58 Logon       Error: 18456, Severity: 14, State: 38.
2014-01-08 18:20:38.58 Logon       Login failed for user 'WWW\prod'. Reason: Failed to open the explicitly specified database 'PROD'. [CLIENT: ]
2014-01-08 18:20:38.65 spid21s     Bypassing recovery for database 'PROD' because it is marked as an inaccessible database mirroring database. A problem exists with the mirroring session. The session either lacks a quorum or the communications links are broken because of problems with links, endpoint configuration, or permissions (for the server account or security certificate). To gain access to the database, figure out what has changed in the session configuration and undo the change.
2014-01-08 18:20:38.67 Logon       Error: 18456, Severity: 14, State: 38.
2014-01-08 18:20:38.67 Logon       Login failed for user 'WWW\prod'. Reason: Failed to open the explicitly specified database 'PROD'. [CLIENT: ]
2014-01-08 18:20:38.67 Logon       Error: 18456, Severity: 14, State: 38.
2014-01-08 18:20:38.67 Logon       Login failed for user 'WWW\prod'. Reason: Failed to open the explicitly specified database 'PROD'. [CLIENT: ]
2014-01-08 18:20:38.67 Logon       Error: 18456, Severity: 14, State: 38.
2014-01-08 18:20:38.67 Logon       Login failed for user 'WWW\prod'. Reason: Failed to open the explicitly specified database 'PROD'. [CLIENT: ]
2014-01-08 18:20:42.78 Logon       Error: 18456, Severity: 14, State: 38.
2014-01-08 18:20:42.78 Logon       Login failed for user 'WWW\prod'. Reason: Failed to open the explicitly specified database 'PROD'. [CLIENT: ]
2014-01-08 18:20:42.78 Logon       Error: 18456, Severity: 14, State: 38.
2014-01-08 18:20:42.78 Logon       Login failed for user 'WWW\prod'. Reason: Failed to open the explicitly specified database 'PROD'. [CLIENT: ]
2014-01-08 18:20:43.01 Logon       Error: 18456, Severity: 14, State: 38.
2014-01-08 18:20:43.01 Logon       Login failed for user 'WWW\prod'. Reason: Failed to open the explicitly specified database 'PROD'. [CLIENT: ]
2014-01-08 18:20:43.13 Logon       Error: 18456, Severity: 14, State: 38.
2014-01-08 18:20:43.13 Logon       Login failed for user 'WWW\prod'. Reason: Failed to open the explicitly specified database 'PROD'. [CLIENT: ]
2014-01-08 18:20:43.13 Logon       Error: 18456, Severity: 14, State: 38.
2014-01-08 18:20:43.13 Logon       Login failed for user 'WWW\prod'. Reason: Failed to open the explicitly specified database 'PROD'. [CLIENT: ]
2014-01-08 18:20:43.41 Logon       Error: 18456, Severity: 14, State: 40.

Log from the Mirror Server


2014-01-08 18:11:22.87 spid3s      SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PROD.mdf] in database [PROD] (6).  The OS file handle is 0x000000000000098C.  The offset of the latest long I/O is: 0x000000133e0000
2014-01-08 18:14:55.96 spid3s      SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PROD_log.mdf] in database [PROD] (6).  The OS file handle is 0x00000000000009BC.  The offset of the latest long I/O is: 0x0000000420a200
2014-01-08 18:16:59.17 spid23s     Error: 1474, Severity: 16, State: 1.
2014-01-08 18:16:59.17 spid23s     Database mirroring connection error 4 '10054(An existing connection was forcibly closed by the remote host.)' for 'TCP://principaldb.mydomain.com:5022'.
2014-01-08 18:17:30.87 spid3s      SQL Server has encountered 20 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PROD.mdf] in database [PROD] (6).  The OS file handle is 0x000000000000098C.  The offset of the latest long I/O is: 0x00000009bf6000
2014-01-08 18:19:37.05 spid33s     Database mirroring is inactive for database 'PROD'. This is an informational message only. No user action is required.
2014-01-08 18:22:30.32 spid23s     Error: 1474, Severity: 16, State: 1.
2014-01-08 18:22:30.32 spid23s     Database mirroring connection error 4 'The connection was closed by the remote end, or an error occurred while receiving data: '64(The specified network name is no longer available.)'' for 'TCP://principaldb.mydomain.com:5022'.
2014-01-08 18:23:03.43 spid29s     Error: 1474, Severity: 16, State: 1.
2014-01-08 18:23:03.43 spid29s     Database mirroring connection error 2 'Connection attempt failed with error: '10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)'.' for 'TCP://principaldb.mydomain.com:5022'.
share|improve this question
    
Was there ever a witness configured? –  Edward Dortland Jan 9 at 17:55
    
No, we've never done the witness setup... our intention has always been just to have a passive mirror sitting in a data center, so we didn't go that route. –  Larry McPhillips Jan 9 at 19:42
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.