Log all errors in queries

Question

I have a database Microsoft SQL Server 2008r2 I have some applications in different programming languages. Some of them are legacy and I would not like to modify. Is there a way to log at server level all query errors, regardless of the application causing it? I would like to know, for each error, the query causing it, the error type, and ideally loginame and hostname.

Answer 1

If you are just looking to log this information, you can set up an Extended Events session and capture the error_reported event. Here is an example:

CREATE EVENT SESSION [ErrorCapture] 
ON SERVER 
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE 
    (
        [severity] >= (11)
    )
) 
ADD TARGET package0.asynchronous_file_target
(
    SET filename=N'C:\SqlServer\Testing\ErrorCapture.xel'
)
WITH 
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
);
GO

ALTER EVENT SESSION [ErrorCapture]
ON SERVER
STATE = START;
GO

To test this out, here is a test error with RAISERROR():

raiserror('This is a test error', 2, 1);
go

Then by looking at the XE log through the sys.fn_xe_file_target_read_file system function, you will be able to see all of the logged errors.

Answer 2

Yes. Monitor for Event Notifications in the Errors and Warnings Event Category. Specifically, the Exception event will be raised for each error. For example, based on the code in MS SQL Server third party transaction blocking monitor tools:

use msdb;
go

create queue [errors];
go

create service errors on queue [errors] (
    [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
go

create event notification [errors]
    on server for EXCEPTION
    to service N'errors', N'current database';
go

If we check the queue, we'll see all exception reported:

receive cast(message_body as xml) from errors;


<EVENT_INSTANCE>
  <EventType>EXCEPTION</EventType>
  <PostTime>2013-02-19T13:52:21.443</PostTime>
  <TextData>Violation of PRIMARY KEY constraint 'PK__x__3213E83F31271CCE'. Cannot insert duplicate key in object 'dbo.x'. The duplicate key value is (1).</TextData>
  ...
  <HostName>...</HostName>
  <ApplicationName>Microsoft SQL Server Management Studio - Query</ApplicationName>
  <LoginName>...</LoginName>
  ...
</EVENT_INSTANCE>