4

I'm attempting to add an Extended Events session to trace backup completion events.

I'm doing this a precursor to performing RESTORE HEADERONLY ... LOADHISTORY on the secondary node of an Availability Group with the intent that both AG nodes have accurate details in the msdb.dbo.backupset and related tables. Contained Availability Groups is not an option since we are using SQL Server 2019. We're also using Distributed Availability Groups, which precludes Contained AGs, so even if we could upgrade to SQL Server 2022, Contained AGs still wouldn't help.

The Extended Event definition is:

CREATE EVENT SESSION [backup_finished]
ON SERVER 
ADD EVENT [D5149520-6282-11DE-8A39-0800200C9A66].[sqlserver].[backup_restore_progress_trace]
(
    ACTION
    (
          [sqlserver].[client_app_name]
        , [sqlserver].[client_hostname]
        , [sqlserver].[database_name]
        , [sqlserver].[nt_username]
        , [sqlserver].[server_principal_name]
        , [sqlserver].[session_id]
        , [sqlserver].[sql_text]
    )
    WHERE 
    (
        /* this is the database name where the BACKUP operation took place, not the database being backed up. */
            [CE79811F-1A80-40E1-8F5D-7445A3F375E7].[sqlserver].[database_name] = N'maintenance' 
        /* this is the name of the database that was backed up */
        AND [database_name] = N'<database_of_interest>'
        /* 'Backup' or 'Restore' */
        AND [operation_type] = N'Backup'
        /* 
            0 -> Information of major steps in the operation
            1 -> Verbose I/O related information 
        */
        AND [trace_level] = 0 
        AND 
        (
            /* the trace_message contains one or more characters at the end */
               [trace_message] LIKE N'BACKUP DATABASE finished%'
            OR [trace_message] LIKE N'BACKUP LOG finished%'
        )
    )
)
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 = OFF
);
GO

[D5149520-6282-11DE-8A39-0800200C9A66] is the module GUID for sqlmin.dll, which contains the code for the [backup_restore_progress_trace] Extended Event.

[CE79811F-1A80-40E1-8F5D-7445A3F375E7] is the module GUID for sqllang.dll, which contains the majority of the [sqlserver] Extended Event code.

In the where clause, I have two predicates for [database_name]; the first one [CE79811F-1A80-40E1-8F5D-7445A3F375E7].[sqlserver].[database_name] = N'maintenance' limits the captured events to statements that were executed in the context of the maintenance database. This predicate is fully qualified with the module GUID to differentiate it from the identically named [database_name] event field name.

The [database_name], [operation_type], [trace_level], and [trace_message] predicates are not fully qualified because I cannot determine how to fully qualify them.

I have tried qualifying the unqualified predicates with every module GUID I can find, however none of them resolve, returning this error when I attempt to create the Extended Event:

Msg 25706, Level 16, State 8, Line 62
The event attribute or predicate source, "6D503F55-3041-4A62-BB8C-57C3CF081B7B.sqlserver.database_name", could not be found.
Msg 15151, Level 16, State 22, Line 105
Cannot alter the event session 'backup_finished', because it does not exist or you do not have permission.

The list of modules, and their guids, are listed in this query:

SELECT 
      [module_guid] = QUOTENAME([dxp].module_guid)
    , [package_name] = QUOTENAME([dxp].[name])
    , [module_file_name] = RIGHT([dolm].[name], CHARINDEX(N'\', REVERSE([dolm].[name])) - 1)
FROM 
    [sys].[dm_xe_packages] [dxp]
    INNER JOIN [sys].[dm_os_loaded_modules] [dolm] ON [dxp].[module_address] = [dolm].[base_address]
ORDER BY 
    [dxp].[name];

Results from my server:

module_guid package_name module_file_name
[CE79811F-1A80-40E1-8F5D-7445A3F375E7] [filestream] sqllang.dll
[5B2DA06D-898A-43C8-9309-39BBBE93EBBD] [package0] sqldk.dll
[30E37A45-E152-4943-9189-2B235FCC4A6F] [qds] qds.dll
[CE79811F-1A80-40E1-8F5D-7445A3F375E7] [SecAudit] sqllang.dll
[CE79811F-1A80-40E1-8F5D-7445A3F375E7] [sqlclr] sqllang.dll
[5B2DA06D-898A-43C8-9309-39BBBE93EBBD] [sqlos] sqldk.dll
[CE79811F-1A80-40E1-8F5D-7445A3F375E7] [SQLSatellite] sqllang.dll
[6D503F55-3041-4A62-BB8C-57C3CF081B7B] [sqlserver] sqllang.dll
[CE79811F-1A80-40E1-8F5D-7445A3F375E7] [sqlserver] sqllang.dll
[D5149520-6282-11DE-8A39-0800200C9A66] [sqlserver] sqlmin.dll
[CE79811F-1A80-40E1-8F5D-7445A3F375E7] [sqlsni] sqllang.dll
[CE79811F-1A80-40E1-8F5D-7445A3F375E7] [ucs] sqllang.dll
[C2FE5706-A411-4F95-AFB1-5430439C8DE6] [xesvlpkg] svl.dll
[DD554AF1-C3F5-4C5E-8F83-C31C49957F3F] [XtpCompile] hkcompile.dll
[D0234D96-8A83-4636-A717-41459AF88D71] [XtpEngine] hkengine.dll
[DE0EFEC2-B9F3-45C8-9FCA-8247CB57109C] [XtpRuntime] hkruntime.dll

Does anyone know what I should use to fully qualify the unqualified predicates?

2 Answers 2

5

You can qualify the fields with the event name to distinguish them.

e.g. [backup_restore_progress_trace].[database_name] versus [sqlserver].[database_name].

Full session:

CREATE EVENT SESSION [backup_finished] 
ON SERVER 
ADD EVENT sqlserver.backup_restore_progress_trace
(
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.[database_name],
        sqlserver.nt_username,
        sqlserver.server_principal_name,
        sqlserver.[session_id],
        sqlserver.sql_text
    )
    WHERE 
    (
        (
            (
                (
                    (
                        [sqlserver].[database_name]=N'maintenance'
                    )
                    AND 
                    (
                        [backup_restore_progress_trace].[database_name]=N'database_of_interest'
                    ) 
                ) 
                AND 
                (
                    [backup_restore_progress_trace].[operation_type]=(0)
                )
            ) 
            AND 
            (
                [backup_restore_progress_trace].[trace_level]=(0)
            )
        ) 
        AND 
        (
            [sqlserver].[like_i_sql_unicode_string]
            (
                [backup_restore_progress_trace].[trace_message],
                N'BACKUP DATABASE finished%'
            )
            OR
            [sqlserver].[like_i_sql_unicode_string]
            (
                [backup_restore_progress_trace].[trace_message],
                N'BACKUP LOG finished%'
            )
        )
    )
);
3
+200

Recap

To simplify the question: There is a conflicting column named database_name (one is from global action and the other is the event's column).

You want to be deterministic, so it makes sense you want to add more qualifications. But using less actually works.

  • The action column refers to the database where the code is being run
  • The event column refers to the target of the backup/restore operation

The event column doesn't have to be qualified. The action column should be qualified with sqlserver. (even though they both live in the same package).

I'm guessing there is some short-circuit logic that searches the nearest context first.

MVP

/* minimal definition */
DROP EVENT SESSION [backup_restore_progress_trace] ON SERVER 
go
CREATE EVENT SESSION [backup_restore_progress_trace] ON SERVER 
ADD EVENT sqlserver.backup_restore_progress_trace
(
    ACTION
    (
        sqlserver.[database_name]
    )
    WHERE 
        [database_name] = N'msdb'
        AND sqlserver.[database_name] = N'master'
)
GO

/* ran a backup command of msdb from the context of master db - this should be captured */
USE master
go
BACKUP database msdb TO DISK = 'NUL'

* ran a backup command of msdb from the context of msdbdb - this should NOT be captured */
go
USE msdb
go
BACKUP database msdb TO DISK = 'NUL'

This works as expected.

enter image description here

Setting the filters in the GUI shows us the same thing.

enter image description here

Scripting it out of GUI would give us TSQL similar to the example above (albeit with way more parenthesis)

Module guid

I've tested the fully qualified path as well. The CREATE SESSION documentation mentions the optional module_guid and from my testing it works:

  • on the event level
  • for the action database_name

it didn't work for any of the predicates - same as in your question.

I don't see why it shouldn't work, but it doesn't. I've captured the error_reported with a callstack action and got this (I've indented the interesting part)

sqllang!XeSqlPkg::CollectClientAppNameActionInvoke
sqllang!XeSqlPkg::CollectSessionIdActionInvoke
sqllang!XeSqlPkg::CollectSqlText<XE_ActionForwarder>
sqllang!XeSqlPkg::CollectSqlTextActionInvoke
sqllang!XeSqlPkg::CollectTSqlStack<XE_ActionForwarder>
sqllang!XeSqlPkg::CollectTSqlStackActionInvoke
sqllang!XeSqlPkg::error_reported::Publish
sqllang!ErrorReportedAutoPublish::Publish
sqllang!CErrorReportingManager::CwchFormatAndPrint
sqllang!ex_vcallprint_no_dump
sqllang!MSQLErrorHandlerFunc
sqldk!CallExceptionHandler
    sqldk!ex_raise2
    sqldk!ex_raise
    sqllang!CXE_PredicateCompiler::CompilePredicate
    sqllang!CXE_PredicateCompiler::CompilePredicate
    sqllang!CXE_Predicate::Bind
    sqllang!CXE_Event::Bind
    sqllang!XEventController::StoreEventSession
sqllang!CStmtCreateEventSession::XretExecute
sqllang!CMsqlExecContext::ExecuteStmts<0,1>
sqllang!CMsqlExecContext::FExecute
sqllang!CSQLSource::Execute
sqllang!process_request

So it fails during binding, but I don't think we can get more info than that. The only other article I found about this was this: Secrets, hints, and tips for SQL Server Extended Events

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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