title: Our SQL Server stored procedure "dies" - but how can I find the cause?
body: <p>We have a <em>SQL Server</em> Stored Procedure which does a very long, painful <code>INSERT INTO.. SELECT FROM...</code> command.</p>
  
  <p>It then writes a status message, via a second stored procedure, to say if it was successful or not.</p>
  
  <pre><code>BEGIN TRY
  
      EXEC [Add_To_Log_Table] 'Starting the INSERT command...'
  
      INSERT INTO [Very_Large_Table]
      SELECT /* About 30 fields */
      FROM /* Lots of tables */ 
  
      EXEC [Add_To_Log_Table] 'The INSERT was successful.'
  
  END TRY
  
  BEGIN CATCH
      EXEC [Add_To_Log_Table] 'The INSERT failed, ' + ERROR_MESSAGE()
  END CATCH
  </code></pre>
  
  <p>When our data set is small, this all works beautifully.  The <code>[Very_Large_Table]</code> gets populated, and our <code>'The INSERT was successful.'</code> message gets written to our log table.</p>
  
  <p>But sometimes, when we are dealing with very large data sets, the '<code>Starting the INSERT command...</code>' message gets written to our log file, Activity Monitor shows that the <code>INSERT</code> <em>is</em> running, but after several minutes, the Stored Procedure just "dies".</p>
  
  <p>The <code>CATCH</code> command simply doesn't kick in, so we have no way to detect or recover from this situation.  Is there a way to find out what was the cause ?</p>
  
  <p>I know that <em>SQL Server Management Studio</em> does have some logs (Management \ SQL Server Logs) but these don't show any errors occurring.</p>
  
  <p>I was wondering if SQL Server somehow stores statuses/most recent error messages in one of its internal tables ?</p>
  
  <p>We're running SQL Server 2008 R2, on Windows Server 2008 R2, there's plenty of free hard disc space on the drives used by our database, and the database recovery mode is set to SIMPLE.</p>