Log all errors in queries

I have a Microsoft SQL Server 2008 R2 database.

I have some applications in different programming languages. Some of them are legacy and I would not like to modify them.

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.

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.

StackExchange Database Administrators Q#35015, answer score: 9