0

Using SQL Server 2014:

I am going through the following article that includes useful patterns for TSQL error handling:

https://msdn.microsoft.com/en-IN/library/ms175976.aspx

I like to log errors so later on I can query, monitor, track and inspect the errors took place in my application's store procedures.

I was thinking to create a table and insert the error details as a row into the table in the CATCH block; however I am concern this might not be a good pattern OR there might be a built-in SQL server feature that can log the errors generated by the ;THROW statement.

What would be the best way to log the errors?

Update 1

I should mention that I always set XACT_ABORT on top of my SPs:

SET XACT_ABORT, NOCOUNT ON

Is it safe to assume that there is no way to log errors when XACT_ABORT is ON?

Update 2

The SET XACT_ABORT ON is according to this post:

http://www.sommarskog.se/error_handling/Part1.html#jumpXACT_ABORT

Can xp_logevent be a better alternative than adding an error record to a log table?

Allan Xu
  • 7,998
  • 11
  • 51
  • 122

2 Answers2

1

You have to be very careful with logging from CATCH locks. First and foremost, you must check the XACT_STATE() and honor it. If xact_state is -1 ( 'uncommittable transaction') you cannot do any transactional operation, so the INSERT fail. You must first rollback, then insert. But you cannot simply rollback, because you may be in xact_state 0 (no transaction) in which case rollback would fail. And if xact_state is 1, you are still in the original transaction, and your INSERT may still be rolled back later and you'll loose all track of this error ever occurring.

Another approach to consider is to generate a user defined profiler event using sp_trace_generateevent and have a system trace monitoring your user event ID. This works in any xact_state state and has the advantage of keeping the record even if the encompassing transaction will roll back later.

I should mention that I always set XACT_ABORT

Stop doing this. Read Exception handling and nested transactions for a good SP pattern vis-a-vis error handling and transactions.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • The XACT_ABORT idea came from this post: http://www.sommarskog.se/error_handling/Part1.html#jumpXACT_ABORT . The problem with sp_trace_generateevent is that it always needs a profiling listener. It does not record the error. Can we use xp_logevent instead? – Allan Xu Jul 14 '16 at 16:08
  • I have a lot of respect for Erland, but on the issue of XACT_ABORT I disagree with him. – Remus Rusanu Jul 14 '16 at 17:07
  • as for the trace listener, there is a way: http://sqlblogcasts.com/blogs/simons/archive/2010/10/17/logging-messages-during-a-transaction.aspx – Remus Rusanu Jul 14 '16 at 17:08
0

Yes it is better. If you want to store then try this.

declare @Error_msg_desc    varchar(500)
,@Error_err_code   int            
,@Error_sev_num int        
,@Error_proc_nm varchar(100)     
,@Error_line_num    int     
begin try
    select 1/0
end try
begin catch
    select   @Error_err_code = ERROR_NUMBER()         
            ,@Error_msg_desc = ERROR_MESSAGE()       
            ,@Error_sev_num  = ERROR_SEVERITY()        
            ,@Error_proc_nm  = ERROR_PROCEDURE()        
            ,@Error_line_num = ERROR_LINE() 
    --create SqlLog Table       
    --Insert into Log Table 
    Insert into Sqllog values(@Error_err_code,@Error_msg_desc,@Error_sev_num,@Error_proc_nm,@Error_line_num)            
end catch
yatin parab
  • 174
  • 6