SQL Server Failures (Network Steve Forum)

SQL Server Failures

Please refer to my other post: Team Foundation Server 2013 Update 4 - Completely crashing

The issue resolved itself, but basically TFS appeared to have triggered a state where SQL Server was unusable to the point an Administrator couldn't even restart the SQL services, and no one could connect to any of the databases. We had to restart the server, and connectivity was available for a short while before the issue reoccurred. All event logs related to TFS point to the fact it couldn't connect to the database, I couldn't find any indication as to why SQL Server went into that state.

Can you please help me troubleshoot what actually caused the issue (via my SQL Logs or Windows logs) so I can prevent it from happening again? What would you like for me to share?

Thanks, Jibba

August 14th, 2015 3:03am

Hello,

Please examine SQL Server Error Log and share with us error messages you find there. Share with us the errors you find on the Event Viewer logs.

https://technet.microsoft.com/en-us/library/ms187885(v=sql.120).aspx (Select appropriate version of SQL Server)

Thank you in advance.

Regards,

Alberto Morillo
SQLCoffee.com

Edited by Alberto MorilloMVP, Moderator 17 hours 32 minutes ago

Free Windows Admin Tool Kit Click here and download it now

August 14th, 2015 9:44am

Here are the only errors appearing in the SQL Logs:

2015-08-13 21:32:41.02 Logon       Error: 18456, Severity: 14, State: 38.
2015-08-13 21:32:41.02 Logon       Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database 'Tfs_Configuration'. [CLIENT: <local machine>]
2015-08-13 21:32:47.28 Logon       Error: 18456, Severity: 14, State: 38.
2015-08-13 21:32:47.28 Logon       Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database 'Tfs_Configuration'. [CLIENT: <local machine>]
2015-08-13 21:32:54.32 Logon       Error: 18456, Severity: 14, State: 38.
2015-08-13 21:32:54.32 Logon       Login failed for user 'CPMTFS\TFSService'. Reason: Failed to open the explicitly specified database 'ReportServer'. [CLIENT: <local machine>]
2015-08-13 21:32:57.30 Logon       Error: 18456, Severity: 14, State: 38.
2015-08-13 21:32:57.30 Logon       Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database 'Tfs_Configuration'. [CLIENT: <local machine>]
2015-08-13 21:32:58.96 Logon       Error: 18456, Severity: 14, State: 38.
2015-08-13 21:32:58.96 Logon       Login failed for user 'CPMTFS\TFSService'. Reason: Failed to open the explicitly specified database 'ReportServer'. [CLIENT: <local machine>]
2015-08-13 21:33:03.32 Logon       Error: 18456, Severity: 14, State: 38.
2015-08-13 21:33:03.32 Logon       Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database 'ReleaseManagement'. [CLIENT: <local machine>]
2015-08-13 21:33:04.91 Logon       Error: 18456, Severity: 14, State: 38.
2015-08-13 21:33:04.91 Logon       Login failed for user '[Server Name]\TFSService'. Reason: Failed to open the explicitly specified database 'ReportServer'. [CLIENT: <local machine>]
2015-08-13 21:33:07.02 Logon       Error: 18456, Severity: 14, State: 38.
2015-08-13 21:33:07.02 Logon       Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database 'Tfs_Configuration'. [CLIENT: <local machine>]

I have thousands of Release Management errors, some of which are provided below. Immediately following these are approx. 10 of the same error, the MSSQLSERVER Source events you see below:

Log Name:      Application
Source:        Release Management Services
Date:          8/13/2015 3:59:28 PM
Event ID:      0
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      [Server Name]
Description:
Timestamp: 8/13/2015 3:59:28 PM
Message: Error thrown while executing the stored procedure SystemSettings_Get with parameters , @PartitionId:1.
Category: General
Priority: -1
EventId: 0
Severity: Error
Title:
Machine: [Server Name]
Application Domain: /LM/W3SVC/3/ROOT-1-130839798237840000
Process Id: 1972
Process Name: C:\Windows\SysWOW64\inetsrv\w3wp.exe
Win32 Thread Id: 4608
Thread Name: 
Extended Properties: 
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="Release Management Services" />
    <EventID Qualifiers="0">0</EventID>
    <Level>2</Level>
    <Task>0</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2015-08-13T22:59:28.000000000Z" />
    <EventRecordID>1073899</EventRecordID>
    <Channel>Application</Channel>
    <Computer>[Server Name]</Computer>
    <Security />
  </System>
  <EventData>
    <Data>Timestamp: 8/13/2015 3:59:28 PM
Message: Error thrown while executing the stored procedure SystemSettings_Get with parameters , @PartitionId:1.
Category: General
Priority: -1
EventId: 0
Severity: Error
Title:
Machine: [Server Name]
Application Domain: /LM/W3SVC/3/ROOT-1-130839798237840000
Process Id: 1972
Process Name: C:\Windows\SysWOW64\inetsrv\w3wp.exe
Win32 Thread Id: 4608
Thread Name: 
Extended Properties: </Data>
  </EventData>
</Event>

Log Name:      Application
Source:        Release Management Services
Date:          8/13/2015 3:59:28 PM
Event ID:      0
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      [Server Name]
Description:
Timestamp: 8/13/2015 3:59:28 PM
Message: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.): \r\n\r\n   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.TeamFoundation.Release.Sql.OnPrem.OnPremDbAccessor.ExecuteXmlStoredProcedure(String storedProcedureName, Hashtable xmlParameters, String rootElementName, Boolean addRequestorParameter)
Category: General
Priority: -1
EventId: 0
Severity: Error
Title:
Machine: [Server Name]
Application Domain: /LM/W3SVC/3/ROOT-1-130839798237840000
Process Id: 1972
Process Name: C:\Windows\SysWOW64\inetsrv\w3wp.exe
Win32 Thread Id: 4608
Thread Name: 
Extended Properties: 
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="Release Management Services" />
    <EventID Qualifiers="0">0</EventID>
    <Level>2</Level>
    <Task>0</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2015-08-13T22:59:28.000000000Z" />
    <EventRecordID>1073900</EventRecordID>
    <Channel>Application</Channel>
    <Computer>[Server Name]</Computer>
    <Security />
  </System>
  <EventData>
    <Data>Timestamp: 8/13/2015 3:59:28 PM
Message: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.): \r\n\r\n   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal&amp; connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal&amp; connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal&amp; connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.TeamFoundation.Release.Sql.OnPrem.OnPremDbAccessor.ExecuteXmlStoredProcedure(String storedProcedureName, Hashtable xmlParameters, String rootElementName, Boolean addRequestorParameter)
Category: General
Priority: -1
EventId: 0
Severity: Error
Title:
Machine: [Server Name]
Application Domain: /LM/W3SVC/3/ROOT-1-130839798237840000
Process Id: 1972
Process Name: C:\Windows\SysWOW64\inetsrv\w3wp.exe
Win32 Thread Id: 4608
Thread Name: 
Extended Properties: </Data>
  </EventData>
</Event>

Log Name:      Application
Source:        MSSQLSERVER
Date:          8/13/2015 3:59:29 PM
Event ID:      18056
Task Category: Server
Level:         Error
Keywords:      Classic
User:          NETWORK SERVICE
Computer:      [Server Name]
Description:
The client was unable to reuse a session with SPID 80, which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="MSSQLSERVER" />
    <EventID Qualifiers="49152">18056</EventID>
    <Level>2</Level>
    <Task>2</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2015-08-13T22:59:29.000000000Z" />
    <EventRecordID>1073902</EventRecordID>
    <Channel>Application</Channel>
    <Computer>[Server Name]</Computer>
    <Security UserID="S-1-5-20" />
  </System>
  <EventData>
    <Data>80</Data>
    <Data>23</Data>
    <Binary>884600001400000007000000430050004D00540046005300000000000000</Binary>
  </EventData>
</Event>

This appears to be the error associated with the triggering collapse:

Log Name:      Application
Source:        VSTTController
Date:          8/13/2015 3:11:41 PM
Event ID:      0
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      [Server Name]
Description:
(QTController.exe, PID 2380, Thread 12) DEPLOY: PollDeploymentRuns : Handled Exception Microsoft.TeamFoundation.Framework.Client.DatabaseOperationTimeoutException: TF246018: The database operation exceeded the timeout limit and has been cancelled. Verify that the parameters of the operation are correct. ---> System.Web.Services.Protocols.SoapException: TF246018: The database operation exceeded the timeout limit and has been cancelled. Verify that the parameters of the operation are correct.
   --- End of inner exception stack trace ---
   at Microsoft.TeamFoundation.Client.Channels.TfsHttpClientBase.HandleReply(TfsClientOperation operation, TfsMessage message, Object[]& outputs)
   at Microsoft.TeamFoundation.Client.Channels.TfsHttpClientBase.Invoke(TfsClientOperation operation, Object[] parameters, TimeSpan timeout, Object[]& outputs)
   at Microsoft.TeamFoundation.Lab.Client.LabProxyBase.MakeSoapCall[T](String bodyName, ToXml toXml, FromXml`1 fromXml)
   at Microsoft.TeamFoundation.Lab.Client.LabServiceProxy.QueryLabSystemDeploymentTasks(IEnumerable`1 labSystemIds, String testControllerName)
   at Microsoft.TeamFoundation.Lab.Client.LabService.QueryLabSystemDeploymentTasks(IEnumerable`1 labSystemIds, String testControllerName)
   at Microsoft.VisualStudio.QualityTools.Controller.DeploymentTaskMonitor.ProcessNewDeploymentTasks()
   at Microsoft.VisualStudio.QualityTools.Controller.DeploymentTaskMonitor.Poll(Object paramater)
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="VSTTController" />
    <EventID Qualifiers="0">0</EventID>
    <Level>2</Level>
    <Task>0</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2015-08-13T22:11:41.000000000Z" />
    <EventRecordID>1071326</EventRecordID>
    <Channel>Application</Channel>
    <Computer>[Server Name]</Computer>
    <Security />
  </System>
  <EventData>
    <Data>(QTController.exe, PID 2380, Thread 12) DEPLOY: PollDeploymentRuns : Handled Exception Microsoft.TeamFoundation.Framework.Client.DatabaseOperationTimeoutException: TF246018: The database operation exceeded the timeout limit and has been cancelled. Verify that the parameters of the operation are correct. ---&gt; System.Web.Services.Protocols.SoapException: TF246018: The database operation exceeded the timeout limit and has been cancelled. Verify that the parameters of the operation are correct.
   --- End of inner exception stack trace ---
   at Microsoft.TeamFoundation.Client.Channels.TfsHttpClientBase.HandleReply(TfsClientOperation operation, TfsMessage message, Object[]&amp; outputs)
   at Microsoft.TeamFoundation.Client.Channels.TfsHttpClientBase.Invoke(TfsClientOperation operation, Object[] parameters, TimeSpan timeout, Object[]&amp; outputs)
   at Microsoft.TeamFoundation.Lab.Client.LabProxyBase.MakeSoapCall[T](String bodyName, ToXml toXml, FromXml`1 fromXml)
   at Microsoft.TeamFoundation.Lab.Client.LabServiceProxy.QueryLabSystemDeploymentTasks(IEnumerable`1 labSystemIds, String testControllerName)
   at Microsoft.TeamFoundation.Lab.Client.LabService.QueryLabSystemDeploymentTasks(IEnumerable`1 labSystemIds, String testControllerName)
   at Microsoft.VisualStudio.QualityTools.Controller.DeploymentTaskMonitor.ProcessNewDeploymentTasks()
   at Microsoft.VisualStudio.QualityTools.Controller.DeploymentTaskMonitor.Poll(Object paramater)</Data>
  </EventData>
</Event>

Thanks, Jibba

August 14th, 2015 1:35pm

This topic is archived. No further replies will be accepted.