title: Debugging linked server that only runs short period of time on SQL Server 2014
body: <p>On an SQL Server 2008 (latest version) I have a linked server to an Excel file that works smooth, using following settings:</p>
  
  <pre><code>@srvproduct=N'ACE 12.0', @provider=N'Microsoft.ACE.OLEDB.12.0' and @provstr=N'Excel 12.0; HDR=Yes'
  </code></pre>
  
  <p>Moreover, query and connect timeout are set to 30s.</p>
  
  <p>The moment I generate the <strong>exact</strong> same linked server on SQL Server 2014  (latest version), the linked server works fine. </p>
  
  <p>Unfortunately, after around a day, the connection from SQL Server 2014 to the Excel file stops working. For example, when right-clicking on the linked server and clicking on <em>Test Connection</em>,  SQL Server Management Studio freezes for around half an hour. The error I get is following:</p>
  
  <hr>
  
  <p>The test connection to the linked server failed.</p>
  
  <p>===================================</p>
  
  <p>An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)</p>
  
  <hr>
  
  <p>Program Location: 
  at  Microsoft.SqlServer.Management.Common.ServerConnection. ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
  at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery (String cmd, Boolean retry)
     at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
     at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer. LinkedServer-ConnectionTest.Invoke()</p>
  
  <p>===================================</p>
  
  <p>Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. (.Net SqlClient Data Provider)</p>
  
  <p>Error Number: -2
  Severity: 11
  State: 0</p>
  
  <hr>
  
  <p>Program Location:</p>
  
  <p>at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
     at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)</p>
  
  <p>===================================</p>
  
  <p>The wait operation timed out</p>
  
  <hr>
  
  <p>My only solution - so far - is to restart the "SQL Server" service. After having done this, also the linked server works again.</p>
  
  <p>Is there e.g. a way to debug at what point the linked server stops working?</p>