SQL Server – How to Log All Errors in Queries

sql serversql-server-2008-r2

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.

Best Answer

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.

Related Solutions

Sql-server – Mysterious disappearing consistency errors in SharePoint DBs

I would be confident you have corruption. Sounds like at least one corrupt data page. What does the contents of msdb.dbo.suspect_pages look like? Refer to http://msdn.microsoft.com/en-us/library/ms191301(v=sql.105).aspx for parsing some of that info.

Sql-server – Log all than queries taking more than X seconds

The syntax you're looking for will be very similar to this:


create event session longrunning_statements on server
add event sqlserver.sql_statement_completed
(
where (duration > 5000000)
)
add target package0.asynchronous_file_target
(Set filename='c:\capture\xe_longrunning_statement.xel', 
metadatafile='c:\capture\xe_longrunning_statement.xem');

alter event sesession longrunning_statements on server state=start;

This will set up an event session which will track an sql statement that takes longer than 5 seconds to complete. The duration filter is based on microseconds btw.

If you wanted to restrict this to a specific database (in this case the db with database id=5, then you could change the filter to:

where (duration > 5000000 and database_id=5)

Lots of other filters as well to tie it down to specific sessions or users if required.

This event session will log it's results out to a file (c:\capture\xe_longrunning_statement.xel) which can be read in with the fn_xe_file_target_read_file function like so:

SELECT *
    FROM
        sys.fn_xe_file_target_read_file
        (
            'c:\capture\xe_longrunning_statement*.xel',
            'c:\capture\xe_longrunning_statement.xem',
            null,
            null
        );

(Note that I've used 2008R2 standards for tge xel/xem files and setting up the target. It's slightly different in 2012, but this syntax would still work). The * in the filename is to let the function know you want to load up all of the files if they've rolled over due to size.

You'll then need to parse the data out through your XML tools of choice.

Once you're done with the capture, you can tidy up as follows:


alter event session longrunning_statements on server state=stop;
drop event session longrunning_statements;

One of the best online resources is Jonathan Kehayais' 31 days of Exteneded Events - https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-31-days-of-extended-events/ - which walks you through the topic with plenty of examples.

Best Answer

Related Solutions

Sql-server – Mysterious disappearing consistency errors in SharePoint DBs

Sql-server – Log all than queries taking more than X seconds

Related Question