THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences on SQL Server, Business Intelligence and .NET

Trapping SQL Server Errors with Extended Events

One very useful usage of Extended Events is the ability to trap SQL Server error without the need to have a server trace running (which, btw, is deprecated), with the additional feature of being able to query the data as soon as it comes in. This means that we a solution to monitor and trap errors as soon as they happen can be easily created, in order to help developers to fix problems as soon as they are detected. This is really, really, really helpful especially in very big applications, where the code base is quite old and there is no-one really knowing everything of the solution.

To start a Extended Events sessions in order to trap SQL Server errors with severity greater than 10, just run the following script:

CREATE EVENT SESSION [error_trap] ON SERVER
ADD EVENT sqlserver.error_reported
    (
        ACTION    (package0.collect_system_time,package0.last_error,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack,sqlserver.username)
        WHERE    ([severity]>10)
    )
ADD TARGET package0.event_file
    (
        SET filename=N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\XEvents\error_trap.xel'
    )
WITH
    (       
        STARTUP_STATE=OFF
    )
GO

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

The problem with Exended Events is that they only talks XML which is surely flexible and extensible, but not at all confortable to be queried. That’s why I prefer to turn it into something relational. Using the xml nodes function and pivoting data can make the trick:

IF (OBJECT_ID('tempdb..#e') IS NOT NULL) DROP TABLE #e
go

WITH cte AS
(
    SELECT
        CAST(event_data AS XML) AS event_data
    FROM
        sys.fn_xe_file_target_read_file('D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\XEvents\error_trap*.xel', NULL, NULL, NULL)
),
cte2 AS
(
    SELECT
        event_number = ROW_NUMBER() OVER (ORDER BY T.x)
    ,    event_name = T.x.value('@name', 'varchar(100)')
    ,    event_timestamp = T.x.value('@timestamp', 'datetimeoffset')
    ,    event_data
    FROM
        cte   
    CROSS APPLY
        event_data.nodes('/event') T(x)
)
SELECT * INTO #e FROM cte2
go

WITH cte3 AS
(
    SELECT
        c.event_number,
        c.event_timestamp,
        --data_field = T2.x.value('local-name(.)', 'varchar(100)'),
        data_name = T2.x.value('@name', 'varchar(100)'),
        data_value = T2.x.value('value[1]', 'varchar(max)'),
        data_text = T2.x.value('text[1]', 'varchar(max)')
    FROM
        #e c
    CROSS APPLY
        c.event_data.nodes('event/*') T2(x)
),
cte4 AS
(
    SELECT
        *
    FROM
        cte3
    WHERE
        data_name IN ('error_number', 'severity', 'message', 'database_name', 'database_id', 'client_hostname', 'client_app_name', 'collect_system_time', 'username')
)
SELECT
    *
FROM
    cte4
PIVOT
    (MAX(data_value) FOR data_name IN ([error_number], [severity], [message], database_name, database_id, username, client_hostname, client_app_name, collect_system_time)) T
WHERE
    [severity] > 10
ORDER BY
    event_timestamp DESC
go

and voilà, now all errors can be easily identified:

image

It’s now very easy to create reports and dashboards to monitor the system in (near) real time. And this is vital when you run a 24x7 online business.

Published Sunday, March 17, 2013 7:45 PM by Davide Mauri

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Douglas Osborne said:

If I run this on my SQL 2008 R2 server I get

Msg 25623, Level 16, State 3, Line 1

The event action name, "package0.last_error", is invalid, or the object could not be found

Msg 15151, Level 16, State 1, Line 2

Cannot alter the event session 'error_trap', because it does not exist or you do not have permission.

Thoughts?

Doug

April 8, 2013 11:10 AM
 

Carlo Borreo said:

@Douglas Osborne:

I think it depends from the SQL version, I have 2008r2 and I get errors too. Probably some fields, like database_name, need the latest Mssql version.

I changed like this, it now works:

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='J:\ServerXmlOutput\ErrorCapture.xel'

)

WITH

(

   MAX_MEMORY=4096 KB,

   EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,

   MAX_DISPATCH_LATENCY=10 SECONDS,

   MAX_EVENT_SIZE=0 KB,

   MEMORY_PARTITION_MODE=NONE,

   TRACK_CAUSALITY=OFF,

   STARTUP_STATE=ON

);

November 8, 2013 9:54 AM
 

Carlo Borreo said:

Signor Mauri,

I would like to avoid logging errors from users test1, test2 and so on.

If I change the WHERE like this it works:

WHERE ( [severity] >= 11 AND sqlserver.username<>'test1' AND sqlserver.username<>'test2' AND sqlserver.username<>'test3' ... )

But, this is not practical.

If I try to use SUBSTRING, or LIKE, or NOT LIKE this triggers a syntax error.

Do you have a better workaround?

November 8, 2013 9:59 AM
 

Davide Mauri : Trapping SQL Server Errors with Extended Events | Sladescross's Blog said:

April 27, 2014 7:14 AM

Leave a Comment

(required) 
(required) 
Submit

About Davide Mauri

Davide Mauri - MCP, MCAD, MCDBA, MCT, MVP on SQL Server - has worked with SQL Server since version 6.5, and his interests cover the whole platform, from the Relational Engine to Analysis Services, from architecture definition to performance tuning. He also has a strong knowledge of XML, .NET and the Object Oriented Design principles, which allows him to have the correct vision and experience to handle development of complex business intelligence solutions. Having worked as a Microsoft Certified Teacher for many years, Davide is able to pass all his knowledge to his co-workers, allowing his team to deliver high-quality solutions. He currently works as a Mentor for SolidQ and can be found speaking in many Italian and internationals events.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement