0

Is there a trace configuration or setting that will enable the profiler to output ERROR_PROCEDURE() and ERROR_LINE() information that is always available when writing manual error handling in CATCH blocks?

enter image description here

Doing a full trace of every statement being executed is very overwhelming on active systems (I got 45k entries trying it on my test system), and even then you have to guess where the error came from based on the preceding entries.

  • 1
    Have you checked extended events? It's supposed to be replacing the profiler. I haven't done exactly what you're trying to do, but a few months back I used extended events to catch a procedure that was erroring out. – Alen Jun 18 '18 at 19:22
  • Once we upgrade to SSMS 17.3 or newer I'll be all over that tool! – Elaskanator Aug 6 '18 at 19:51
3

If you're able to use Extended Events, this is a lot easier. As you said:

Doing a full trace of every statement being executed is very overwhelming on active systems

You can set up the XE session like this:

CREATE EVENT SESSION exceptions
    ON SERVER
    ADD EVENT sqlserver.error_reported
    ( ACTION ( sqlserver.sql_text, sqlserver.tsql_frame ))
    ADD TARGET package0.event_file
    ( SET filename = N'c:\temp\exceptions' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 5 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = OFF,
           STARTUP_STATE = OFF );
GO

A simple proc to throw an error:

CREATE PROCEDURE dbo.error_out
AS 
BEGIN
    SELECT CASE WHEN 1 = 1 
                THEN 1 /0 
                ELSE NULL 
           END
END
GO 

Start the session and run the procedure a couple few times:

ALTER EVENT SESSION exceptions ON SERVER STATE = START;
GO 

EXEC dbo.error_out;
GO 

Then check your XE session:

SELECT CONVERT(XML, x.event_data) AS x
INTO #xml
FROM   sys.fn_xe_file_target_read_file('c:\temp\exceptions*.xel', NULL, NULL, NULL) AS x;

SELECT  x.x.value('(event/@timestamp)[1]', 'DATETIME2(7)') AS event_date,
        x.x.value('(event/data[@name="message"]/value)[1]', 'VARCHAR(2044)') AS message,
        x.x.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(2044)') AS sql_text,
        ca.c.value('(@line)[1]', 'INT') AS line_number,
        ca.c.value('(@offsetStart)[1]', 'INT') AS offset_start,
        ca.c.value('(@offsetEnd)[1]', 'INT') AS offset_end
FROM #xml AS x
CROSS APPLY x.nodes('//frame') AS ca (c)

You should get results something like this:

NUTS

  • A large concern is the data volume. I see in your sample results that the USE [master] statement was logged which does not look like it would be applicable to the sqlserver.error_reported event (perhaps you were just testing). Another thing I see spamming my trace are "soft-errors" about temporary tables that are not declared by the referencing stored procedure (as per code spaghetti, the caller defines them to capture results). How do I filter those out? – Elaskanator Jun 18 '18 at 21:18
  • @Elaskanator there are a lot of ways to filter a lot of things out. You'd have to be more specific about what you'd like removed. – Erik Darling Jun 18 '18 at 21:21
  • This is writing an entry each time one of the 10-second SQL Agent jobs run (invariant of the result). I'm only interested in capturing exceptions. Apparently error_reported includes "outcomes" universally? – Elaskanator Jun 18 '18 at 21:28
  • @Elaskanator What I'm getting at is you'd need to give me some attribute to filter things by. Error number, error severity, etc. If possible, you may want to poke around the XE GUI to see for yourself what's available. – Erik Darling Jun 18 '18 at 22:27
  • 1
    @Elaskanator I'm totally here to help, I just need to know how to do it. If you come up with any questions, please do ask. – Erik Darling Jun 20 '18 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.