go backsqlschool blogs list

TRY… CATCH with Extended Event

by Antonios Chatzipavlis

Από το SQL Server 2005 έχουμε στην διάθεση μας το TRY…CATCH με το οποίο μπορούμε να έχουμε ένα δομημένο exception handling και πραγματικά είμαστε όλοι ευχαριστημένοι με αυτό.

Παρόλο που αυτό κάνει εξαιρετικά την δουλειά του και πιάνει πραγματικά όλα τα run time exceptions υπάρχει κάτι που συμβαίνει και που οι περισσότεροι πιστεύουν ότι είναι limitation σε αυτό (κατά την γνώμη μου δεν είναι).

The problem

Για να δούμε όμως μια τέτοια περίπτωση ώστε να γίνει κατανοητό το πρόβλημα.

Έστω ότι θέλουμε να πάρουμε backup μια database και έχουμε γράψει λάθος το όνομα αυτής ή αυτή δεν υπάρχει για παράδειγμα:

BACKUP DATABASE db TO DISK = 'D:\TEMP\db_full.bak';

Εκτελώντας το command θα πάρουμε τα εξής exceptions:

Msg 911, Level 16, State 11, Line 1
Database 'db' does not exist. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Τα οποία είναι φυσικά αρκετά κατατοπιστικά

Ας δούμε ακόμα ένα παράδειγμα πάλι με backup αλλά αυτή την φορά έχουμε λάθος στο path το οποίο δεν υπάρχει:

BACKUP DATABASE demo TO DISK = 'D:\TEMP2\demo_full.bak';

Τα exceptions τα οποία θα πάρουμε και είναι σαφέστατα είναι τα παρακάτω:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'c:\temp2\demo_full.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Αν κάποιο από αυτά τα commands τα βάλουμε στο TRY…CATCH όπως στο παράδειγμα που ακολουθεί:

BEGIN TRY
       BACKUP DATABASE db TO DISK = 'c:\temp2\demo_full.bak';
END TRY
BEGIN CATCH
       SELECT ERROR_NUMBER() AS err_number,
              ERROR_SEVERITY() AS err_severity,
              ERROR_STATE() AS err_state,
              ERROR_LINE() AS err_line,
              ERROR_MESSAGE() AS err_message
END CATCH

Τότε οι ERROR functions που έχουμε μέσα στο CATCH θα μας γυρίσουν το εξής αποτέλεσμα:

Err_number = 3013, Err_severity = 16 , Err_state = 1 , Err_message = 'BACKUP DATABASE is terminating abnormally.'

Από αυτό βέβαια δεν είναι εύκολο να καταλάβουμε το πραγματικό exception.

Το "πρόβλημα" δεν είναι ακριβώς στο TRY…CATCH αλλά κυρίως στις ERROR functions που επιστρέφουν μόνο το τελευταίο exception.

Όσοι έχουμε ασχοληθεί με το .ΝΕΤ και το αντίστοιχο try…catch σε αυτό ξέρουμε ότι υπάρχει η SQLException class (System.Data.SqlClient) στην οποία υπάρχει το Errors property με το οποίο μπορούμε να πάρουμε όλα τα λάθη που έχουν ενεργοποιηθεί και φυσικά μπορούμε με αυτό τον τρόπο να καταλάβουμε πολλά περισσότερα για το exception.

Θα θέλαμε να υπάρχει κάτι τέτοιο ενσωματωμένο στο SQL Server αλλά ακόμα δυστυχώς δεν υπάρχει (άλλα πιέζουμε product group να βρει χρόνο να το κάνει).

Workaround

Μέχρι αυτό να υλοποιηθεί και με την προϋπόθεση ότι είμαστε σε SQL Server 2008 R2 και πάνω μπορούμε να χρησιμοποιήσουμε τα Extended Events.

Στο σημείο αυτό θα πρέπει τονίσω ότι κάτι τέτοιο δεν χρειάζεται να το κάνουμε για εργασίες που επιστρέφουν ένα και μόνο ένα exception όπως τα INSERT/UPDATE/DELETE. Συνήθως εργασίες που επιστρέφουν περισσότερα από ένα exceptions είναι οι BACKUP/RESTORE/DBCC COMMANDS.

Create Extended Event Session

Αρχικά θα πρέπει να φτιάξουμε ένα Extended Event Session στο οποίο να καταγράφουμε τα SQL Server exception και αυτό μπορεί να γίνει με το event sqlserver.error_reported και στο οποίο προσθέτουμε να καταγράφεται το sessionid και το sql command text αλλά επειδή δεν θέλουμε για όλα τα άλλα session κάνουμε filtering μόνο για το τρέχον session.

Επειδή θέλω και το όνομα να περιέχει το sessionid αλλά επειδή το @spid function δεν μπορεί να μπει στο filtering χρησιμοποιώ dynamic sql. Για την ευκολία μου όλο αυτό το έχω κάνει stored procedure η οποία είναι η παρακάτω.

CREATE proc [dbo].[CreateXE_ErrTrapSession]
as
begin


       if  exists (select * from sys.dm_xe_sessions where name = N'ErrorTrappingSession'+cast(@@spid as nvarchar(6)))
       begin 
              exec master.[dbo].[DropXE_ErrTrapSession];
       end


       declare @XECreateCmd nvarchar(max) = N'CREATE EVENT SESSION [ErrorTrappingSession@SPID]
       ON SERVER 
       ADD EVENT sqlserver.error_reported
       (
              ACTION(
                     sqlserver.session_id,
                     sqlserver.sql_text
              )
              WHERE [package0].[not_equal_unicode_string]([message],N'''') 
              AND [severity]>(10) 
              AND [sqlserver].[session_id]=@SPID
       ) 
       ADD TARGET package0.ring_buffer
       WITH (
    
              EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,

              MAX_MEMORY=4096 KB,
              MAX_DISPATCH_LATENCY=1 SECONDS,
              MAX_EVENT_SIZE=0 KB,
              MEMORY_PARTITION_MODE=NONE,
              TRACK_CAUSALITY=ON,
              STARTUP_STATE=OFF
       );

       ALTER EVENT SESSION [ErrorTrappingSession@SPID] ON SERVER STATE=START;'

       set @XECreateCmd = REPLACE(@XECreateCmd,'@SPID',cast(@@SPID as nvarchar(6)))
       
       exec (@XECreateCmd)
end
GO

Drop Extended Event Session

Επειδή δεν πρέπει να μένουν "σκουπίδια" πρέπει να γίνεται drop το extended event και επειδή όπως παραπάνω είπα αυτό περιέχει το sessionid στο name πάλι κάνω την συγκεκριμένη διαδικασία με dynamic sql και το έχω βάλει σε μια stored procedure που είναι η παρακάτω

CREATE proc [dbo].[DropXE_ErrTrapSession]
as
begin
       if  exists (select * from sys.dm_xe_sessions where name = N'ErrorTrappingSession'+cast(@@spid as nvarchar(6)))
       begin 
              declare @XECmd nvarchar(max) = N'ALTER EVENT SESSION [ErrorTrappingSession@SPID] ON SERVER STATE=STOP;
                                               DROP EVENT SESSION [ErrorTrappingSession@SPID] ON SERVER;'

              set @XECmd = REPLACE(@XECmd,'@SPID',cast(@@SPID as nvarchar(6)))
              
              exec (@XECmd)
       end
end
GO

Parse Errors

Τέλος θέλω μέσα στο CATCH block να μπορώ να διαβάσω τα exceptions και για αυτό το λόγο φτιάχνω μια stored procedure που διαβάζει τo XE Event Session και επειδή ήθελα να στέλνω email έχω φτιάξει αυτά να γίνονται σαν html table. Μπορεί εύκολα κάποιος να το αλλάξει και να το κάνει να φέρνει τα exceptions με την μορφή που θέλει.

CREATE proc [dbo].[ParceXE_ErrTrapSession] @TableBodyForEmail nvarchar(max)=N'' output
as
begin


       DECLARE @XEData XML

       SELECT @XEData = CAST(xet.target_data AS XML)
       FROM sys.dm_xe_session_targets AS xet
       JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
       WHERE xe.name = 'ErrorTrappingSession'+cast(@@spid as nvarchar(6));

       IF @XEData.value('(/RingBufferTarget/@totalEventsProcessed)[1]', 'INT') = 0
       BEGIN
              WAITFOR DELAY '00:00:02';

              SELECT @XEData = CAST(xet.target_data AS XML)
              FROM sys.dm_xe_session_targets AS xet
              JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
              WHERE xe.name = 'ErrorTrappingSession53' --+cast(@@spid as nvarchar(6));
       END;

       DECLARE @TableBody NVARCHAR(MAX) = '<table border="1">' +
                                                                     '<tr>' +
                                                                     '<th>Error Number</th>' +
                                                                     '<th>Severity</th>' +
                                                                     '<th>State</th>' +
                                                                     '<th>Message</th>' +
                                                                     '<th>Sql Text</th>' +
                                                                     '<th>Session ID</th>' +
                                                                     '</tr>';

       with tr
       as
       (
              SELECT 
              x.c.value(N'(data[@name="error_number"]/value)[1]', N'NVARCHAR(MAX)') AS ErrorNumber,
              x.c.value(N'(data[@name="severity"]/value)[1]', N'NVARCHAR(MAX)') AS Severity,
              x.c.value(N'(data[@name="state"]/value)[1]', N'NVARCHAR(MAX)') AS [State],
              x.c.value(N'(data[@name="message"]/value)[1]', N'NVARCHAR(MAX)') AS [Message],
              x.c.value(N'(action[@name="sql_text"]/value)[1]', N'NVARCHAR(MAX)') AS SqlText,
              x.c.value(N'(action[@name="session_id"]/value)[1]', N'NVARCHAR(MAX)') AS SessionId
              FROM @XEData.nodes('//RingBufferTarget/event') AS x(c)
       )
       select @TableBody+=(select 
                                 ErrorNumber as td,
                                 Severity as td,
                                 [State] as td,
                                 [Message] as td,
                                 SqlText as td,
                                 SessionId as td
                           from tr
                           for xml auto , elements
                                         )
       
       set @TableBodyForEmail = @TableBody+N'</table>'


end
GO

All together

Όλα αυτά μαζί μου δίνουν την παρακάτω υλοποίηση και εγώ πλέον λαμβάνω ένα email που περιέχει αναλυτικά όσες πληροφορίες χρειάζομαι.

EXEC [dbo].[CreateXE_ErrTrapSession];

BEGIN TRY
       BACKUP DATABASE db TO DISK = 'c:\temp2\demo_full.bak';
END TRY
BEGIN CATCH

       DECLARE @TableBodyForEmail nvarchar(max)=N'';
       EXEC master.[dbo].[ParceXE_ErrTrapSession] @TableBodyForEmail output

       declare @subject nvarchar(255)= N'SUBJECT'
       declare @body nvarchar(max)='';
       set @body =N'<h2>Errors</h2>' + @TableBodyForEmail  ;
       EXEC msdb.dbo.sp_send_dbmail
                    @profile_name = 'DBMAILPROFILE',
                    @recipients = 'sender@company.com',
                    @subject = @subject,
                    @body=@body,
                    @body_format='HTML',
                    @importance ='High',
                    @attach_query_result_as_file = 0; 

END CATCH

EXEC [dbo].[DropXE_ErrTrapSession]


//antonch

Ημερομηνία: 24 April 2017 15:45
Αξιολόγηση: ( 1 )
Κατηγορίες:
Share it:

Αφήστε το σχόλιο σας - Leave your comment

Τα σχόλια έχουν κλείσει.
Επιτρέπονται μόνο τα σχόλια από τα μέλη του SqlSchool.gr.


newsletter subscription

Εάν επιθυμείτε να λαμβάνετε ενημέρωση από εμάς, δώστε μας το e-mail σας.
PASS chapter logo
Official Professional Association for SQL Server (PASS) chapter for Greece
Join to PASS