[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2226
  • Last Modified:

Microsoft SQL 2005 Database Mirroring Issue

Hi

We are having intermittent issues with our Database Mirror using 2 Servers with Windows 2008 R2 with SQL 2005 standard 64bit as the Principle+Mirror and Windows 2003 Server with SQL2005 standard as the witness. About 1 a month it changers over to the Mirror Server and the errors in the log are:

 
07/29/2012 00:41:42      MSSQLSERVER      Information      The mirrored database "Database1" is changing roles from "PRINCIPAL" to "MIRROR" due to Role Syncronization.      Server      1073743304         
07/29/2012 00:41:42      MSSQLSERVER      Error      SQL Server Assertion: File: <srvproc.cpp><c/> line=304 Failed Assertion = '0 <= cTrackedRef'. This error may be timing-related. If the error persists after rerunning the statement<c/> use DBCC CHECKDB to check the database for structural integrity<c/> or restart the server to ensure in-memory data structures are not corrupted.      Server      3221242538         
07/29/2012 00:41:41            Error      [382] Logon to server '(local)' failed (JobManager)                     
07/29/2012 00:41:41            Error      [298] SQLServer Error: 258<c/> Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]                     
07/29/2012 00:41:41            Error      [165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]                     
07/29/2012 00:41:41            Error      [298] SQLServer Error: 258<c/> TCP Provider: Timeout error [258]. [SQLSTATE 08001]                     
07/29/2012 00:41:38      MSSQLSERVER      Information      Starting up database ''Database2.      Server      1073758961         
07/29/2012 00:41:38      MSSQLSERVER      Information      IO Completion Listener (0x9c4) Worker 0x000000000BB5E1C0 appears to be non-yielding on Node 0. Approx CPU Used: kernel 0 ms<c/> user 0 ms<c/> Interval: 15086.      Server      1073759711         
07/29/2012 00:41:38      MSSQLSERVER      Error      The description for Event ID '1073758876' in Source 'MSSQLSERVER' cannot be found.  The local computer may not have the necessary registry information or message DLL files to display the message<c/> or you may not have permission to access them.  The following information is part of the event:'Severity: 16 Error:258<c/> OS: 258 [Microsoft][SQL Native Client]TCP Provider: Timeout error [258]. '      Server      1073758876         
07/29/2012 00:41:29            Error      [382] Logon to server '(local)' failed (ConnUpdateStartExecutionDate)                     
07/29/2012 00:41:29            Error      [298] SQLServer Error: 258<c/> Unable to complete login process due to delay in login response [SQLSTATE 08001]                     
07/29/2012 00:41:29            Error      [165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]                     
07/29/2012 00:41:29            Error      [298] SQLServer Error: 258<c/> Shared Memory Provider: Timeout error [258]. [SQLSTATE 08001]                     
07/29/2012 00:41:29            Error      [382] Logon to server '(local)' failed (JobManager)                     
07/29/2012 00:41:29            Error      [298] SQLServer Error: 258<c/> Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]                     
07/29/2012 00:41:29            Error      [165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]                     
07/29/2012 00:41:29            Error      [298] SQLServer Error: 258<c/> Shared Memory Provider: Timeout error [258]. [SQLSTATE 08001]                     
07/29/2012 00:41:29            Error      [382] Logon to server '(local)' failed (ConnUpdateStartExecutionDate)                     
07/29/2012 00:41:29            Error      [298] SQLServer Error: 258<c/> Unable to complete login process due to delay in login response [SQLSTATE 08001]                     
07/29/2012 00:41:29            Error      [165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]                     
07/29/2012 00:41:29            Error      [298] SQLServer Error: 258<c/> Shared Memory Provider: Timeout error [258]. [SQLSTATE 08001]                     
07/29/2012 00:41:27      MSSQLSERVER      Information      Starting up database 'Database1'.      Server      1073758961         
07/29/2012 00:41:27      MSSQLSERVER      Information      SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.      Server      1073744627         
07/29/2012 00:41:27      MSSQLSERVER      Information      SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.      Server      1073744627         
07/29/2012 00:41:27      MSSQLSERVER      Information      SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.      Server      1073744627         
07/29/2012 00:41:22      MSSQLSERVER      Information      Database mirroring is inactive for database 'Database2' This is an informational message only. No user action is required.      Server      1073743266         
07/29/2012 00:41:22      MSSQLSERVER      Error      SQL Server Assertion: File: <srvproc.cpp><c/> line=303 Failed Assertion = '0 <= cTotalRef'. This error may be timing-related. If the error persists after rerunning the statement<c/> use DBCC CHECKDB to check the database for structural integrity<c/> or restart the server to ensure in-memory data structures are not corrupted.      Server      3221242538         
07/29/2012 00:41:21      MSSQLSERVER      Error      The description for Event ID '1073758876' in Source 'MSSQLSERVER' cannot be found.  The local computer may not have the necessary registry information or message DLL files to display the message<c/> or you may not have permission to access them.  The following information is part of the event:'Severity: 16 Error:258<c/> OS: 258 [Microsoft][SQL Native Client]TCP Provider: Timeout error [258]. '      Server      1073758876         
07/29/2012 00:41:11      MSSQLSERVER      Error      The mirroring connection to "TCP://SQLRMirror" has timed out for database "Database2" after 10 seconds without a response.  Check the service and network connections.      Server      3221226951         
07/29/2012 00:41:11      MSSQLSERVER      Error      The mirroring connection to "TCP://SQLRWitness" has timed out for database "Database3" after 10 seconds without a response.  Check the service and network connections.      Server      3221226951         
07/29/2012 00:41:11      MSSQLSERVER      Information      Database mirroring is inactive for database 'Database1'. This is an informational message only. No user action is required.      Server      1073743266         
07/29/2012 00:41:11      MSSQLSERVER      Error      The mirroring connection to "TCP://SQLWitness" has timed out for database "Database1" after 10 seconds without a response.  Check the service and network connections.      Server      3221226951         
07/29/2012 00:41:11      MSSQLSERVER      Error      The mirroring connection to "TCP://SQLMirror" has timed out for database "Database1" after 10 seconds without a response.  Check the service and network connections.      Server      3221226951         
07/29/2012 00:41:10            Error      [382] Logon to server '(local)' failed (ConnUpdateStartExecutionDate)                     
07/29/2012 00:41:10            Error      [298] SQLServer Error: 258<c/> Unable to complete login process due to delay in login response [SQLSTATE 08001]                     
07/29/2012 00:41:10            Error      [165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]                     
07/29/2012 00:41:10            Error      [298] SQLServer Error: 258<c/> Shared Memory Provider: Timeout error [258]. [SQLSTATE 08001]                     
07/29/2012 00:41:10      MSSQLSERVER      Error      The mirroring connection to "TCP://SQLWitness" has timed out for database "Database2" after 10 seconds without a response.  Check the service and network connections.      Server      3221226951         
07/29/2012 00:41:10      MSSQLSERVER      Information      Database mirroring is inactive for database 'Database3'. This is an informational message only. No user action is required.      Server      1073743266         
07/29/2012 00:41:10      MSSQLSERVER      Error      The mirroring connection to "TCP://SQLMirror" has timed out for database "Navision" after 10 seconds without a response.  Check the service and network connections.      Server      3221226951         
07/29/2012 00:41:05      MSSQLSERVER      Error      The description for Event ID '1073758876' in Source 'MSSQLSERVER' cannot be found.  The local computer may not have the necessary registry information or message DLL files to display the message<c/> or you may not have permission to access them.  The following information is part of the event:'Severity: 16 Error:0<c/> OS: 0 [Microsoft][SQL Native Client]Unable to complete login process due to delay in opening server connection'      Server      1073758876         
07/29/2012 00:41:05      MSSQLSERVER      Error      The description for Event ID '1073758876' in Source 'MSSQLSERVER' cannot be found.  The local computer may not have the necessary registry information or message DLL files to display the message<c/> or you may not have permission to access them.  The following information is part of the event:'Severity: 16 Error:0<c/> OS: 0 [Microsoft][SQL Native Client]Unable to complete login process due to delay in opening server connection'      Server      1073758876         
07/29/2012 00:40:59            Error      [382] Logon to server '(local)' failed (ConnUpdateStartExecutionDate)                     
07/29/2012 00:40:59            Error      [298] SQLServer Error: 233<c/> Communication link failure [SQLSTATE 08S01]                     
07/29/2012 00:40:59            Error      [298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]                     
07/29/2012 00:40:42      DatabaseMail      Error      1) Exception Information<nl/>===================<nl/>Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException<nl/>Message: There was an error on the connection. Reason: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.<c/> connection parameters: Server Name: SQLPrinciple<c/> Database Name: msdb<nl/>Data: System.Collections.ListDictionaryInternal<nl/>TargetSite: Void OpenConnection(Microsoft.SqlServer.Management.Common.SqlConnectionInfo)<nl/>HelpLink: NULL<nl/>Source: DatabaseMailEngine<nl/><nl/>StackTrace Information<nl/>===================<nl/>   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.OpenConnection(SqlConnectionInfo ci)<nl/>   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.OpenConnection(String dbServerName<c/> String dbName<c/> String userName<c/> String password)<nl/>   at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName<c/> String dbServerName<c/> Int32 lifetimeMinimumSec<c/> LogLevel loggingLevel)<nl/><nl/>2) Exception Information<nl/>===================<nl/>Exception Type: System.Data.SqlClient.SqlException<nl/>Errors: System.Data.SqlClient.SqlErrorCollection<nl/>Class: 11<nl/>LineNumber: 0<nl/>Number: -2<nl/>Procedure: <nl/>Server: SQLPrinciple<nl/>State: 0<nl/>Source: .Net SqlClient Data Provider<nl/>ErrorCode: -2146232060<nl/>Message: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.<nl/>Data: System.Collections.ListDictionaryInternal<nl/>TargetSite: Void OnError(System.Data.SqlClient.SqlException<c/> Boolean)<nl/>HelpLink: NULL<nl/><nl/>StackTrace Information<nl/>===================<nl/>   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception<c/> Boolean breakConnection)<nl/>   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)<nl/>   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj<c/> UInt32 error)<nl/>   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult<c/> TdsParserStateObject stateObj)<nl/>   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()<nl/>   at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()<nl/>   at System.Data.SqlClient.TdsParserStateObject.ReadByte()<nl/>   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior<c/> SqlCommand cmdHandler<c/> SqlDataReader dataStream<c/> BulkCopySimpleResultSet bulkCopyHandler<c/> TdsParserStateObject stateObj)<nl/>   at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)<nl/>   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo<c/> String newPassword<c/> Boolean ignoreSniOpenTimeout<c/> Int64 timerExpire<c/> SqlConnection owningObject)<nl/>   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host<c/> String newPassword<c/> Boolean redirectedUserInstance<c/> SqlConnection owningObject<c/> SqlConnectionString connectionOptions<c/> Int64 timerStart)<nl/>   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject<c/> SqlConnectionString connectionOptions<c/> String newPassword<c/> Boolean redirectedUserInstance)<nl/>   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity<c/> SqlConnectionString connectionOptions<c/> Object providerInfo<c/> String newPassword<c/> SqlConnection owningObject<c/> Boolean redirectedUserInstance)<nl/>   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options<c/> Object poolGroupProviderInfo<c/> DbConnectionPool pool<c/> DbConnection owningConnection)<nl/>   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection<c/> DbConnectionPool pool<c/> DbConnectionOptions options)<nl/>   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)<nl/>   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)<nl/>   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)<nl/>   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)<nl/>   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection<c/> DbConnectionFactory connectionFactory)<nl/>   at System.Data.SqlClient.SqlConnection.Open()<nl/>   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.OpenConnection(SqlConnectionInfo ci)      0      0         
07/29/2012 00:40:40            Error      [382] Logon to server .. failed (ConnVerifyProxyPermissions)                     
07/29/2012 00:40:40            Error      [298] SQLServer Error: 233<c/> Client unable to establish connection [SQLSTATE 08001]                     
07/29/2012 00:40:40            Error      [298] SQLServer Error: 233<c/> Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08001]                   
Not sure exactly why it is doing this and i cannot see any connection issues on the Servers, has anyone else had any similar issues?
0
SGWindowcare
Asked:
SGWindowcare
1 Solution
 
Ryan McCauleyCommented:
It looks like the witness server is losing connectivity to the principle at the time and then initiation a failover, which is what's supposed to do in that situation. It sounds like you don't think the server is actually unavailable, though - could there be a network connectivity issue? About halfway through that log, the witness is saying it's gone 10 seconds without a response from the principle, which is the default configured timeout length. Given your network and your tolerance for interruption, you may want to adjust that value (Thanks MSSQLTips.com):

ALTER DATABASE dbName SET PARTNER TIMEOUT 30 --Or some larger value

Open in new window


When you lengthen this timeout, it will make a temporary network interruption (or other server activity that's causing unresponsiveness) less likely to impact your servers, as a 15-second interruption would have triggered a failover with the default settings, but won't trigger a failover at 30 seconds. However, this will also add some lag when there's a legitimate failure - if your principle goes down, the witness will wait longer before initiating the failover to the mirror, causing more downtime for your clients.

Ideally, I'd look to improve the network reliability between the 3 machines, assuming that's the problem. Also, is the failover predictable? If so, that might help troubleshoot either the network issue or some event on the server that's causing it to become unresponsive for that period of time.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now