Extended event to find failing stored procedure

-- Drop if it exists
DROP EVENT SESSION [failing_queries] ON SERVER 
GO

-- Create extended event session
CREATE EVENT SESSION failing_queries ON SERVER
ADD EVENT sqlserver.error_reported (
    ACTION (sqlserver.sql_text
        , sqlserver.tsql_stack
        , sqlserver.database_id
        , sqlserver.username
        )
    WHERE ([severity] > 10)
    )
ADD TARGET package0.asynchronous_file_target (
    SET filename = 'C:\XEventSessions\failing_queries.xel'
    , metadatafile = 'C:\XEventSessions\failing_queries.xem'
    , max_file_size = 5
    , max_rollover_files = 5
    )
    WITH (MAX_DISPATCH_LATENCY = 5 SECONDS)
GO

-- Start the session
ALTER EVENT SESSION failing_queries ON SERVER STATE = START
GO

-- Test it
CREATE PROCEDURE [dbo].[FailProc]
AS
BEGIN
    SET NOCOUNT ON;
        SELECT 1/0;
END

-- Run it
Exec [FailProc];


-- Get info from trace
;WITH events_cte
AS (
    SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), xevents.event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [err_timestamp]
        , xevents.event_data.value('(event/data[@name="severity"]/value)[1]', 'bigint') AS [err_severity]
        , xevents.event_data.value('(event/data[@name="error_number"]/value)[1]', 'bigint') AS [err_number]
        , xevents.event_data.value('(event/data[@name="message"]/value)[1]', 'nvarchar(512)') AS [err_message]
        , xevents.event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text]
        , xevents.event_data
    FROM sys.fn_xe_file_target_read_file('C:\XEventSessions\failing_queries*.xel', 'C:\XEventSessions\failing_queries*.xem', NULL, NULL)
    CROSS APPLY (
        SELECT CAST(event_data AS XML) AS event_data
        ) AS xevents
    )
SELECT *
FROM events_cte
ORDER BY err_timestamp DESC;

Leave a Reply

Your email address will not be published. Required fields are marked *