Where are the session definitions of extended events stored?

Question

Is it msdb, resource, master or local? If I back up my local database against which I run the XEs, would I have backed up my sessions as well? Are there any metadata stored in the system tables too? Thank you all.

Answer 1

You can retrieve them from various extended DMVs. For example, the queries below will return a bunch of information about extended events. Included are some filters commented out:

-- Extended Event Packages
select
    name,
    guid,
    description
from sys.dm_xe_packages
where (capabilities is null or capabilities & 1 = 0) -- ignore private packages for SQL Server internal use

-- Events
select
    p.name as package_name,
    o.name as source_name,
    o.description
from sys.dm_xe_packages as p
    inner join sys.dm_xe_objects as o
        on p.guid = o.package_guid
where
    (p.capabilities is null or p.capabilities & 1 = 0)
and (o.capabilities is null or o.capabilities & 1 = 0)
and o.object_type = 'event'
--and o.name like 's%'

-- Event targets
select
    p.name as package_name,
    o.name as source_name,
    o.description
from sys.dm_xe_packages as p
    inner join sys.dm_xe_objects as o
        on p.guid = o.package_guid
where
    (p.capabilities is null or p.capabilities & 1 = 0)
and (o.capabilities is null or o.capabilities & 1 = 0)
and o.object_type = 'target'

-- Predicate sources
select
    p.name as package_name,
    o.name as source_name,
    o.description
from sys.dm_xe_packages as p
    inner join sys.dm_xe_objects as o
        on p.guid = o.package_guid
where
    (p.capabilities is null or p.capabilities & 1 = 0)
and (o.capabilities is null or o.capabilities & 1 = 0)
and o.object_type = 'pred_source'

-- Predicate comparators
select
    p.name as package_name,
    o.name as source_name,
    o.description
from sys.dm_xe_packages as p
    inner join sys.dm_xe_objects as o
        on p.guid = o.package_guid
where
    (p.capabilities is null or p.capabilities & 1 = 0)
and (o.capabilities is null or o.capabilities & 1 = 0)
and o.object_type = 'pred_compare'

-- Maps
select
    p.name as package_name,
    o.name as source_name,
    o.description
from sys.dm_xe_packages as p
    inner join sys.dm_xe_objects as o
        on p.guid = o.package_guid
where
    (p.capabilities is null or p.capabilities & 1 = 0)
and (o.capabilities is null or o.capabilities & 1 = 0)
and o.object_type = 'map'

-- Types
select
    p.name as package_name,
    o.name as source_name,
    o.description
from sys.dm_xe_packages as p
    inner join sys.dm_xe_objects as o
        on p.guid = o.package_guid
where
    (p.capabilities is null or p.capabilities & 1 = 0)
and (o.capabilities is null or o.capabilities & 1 = 0)
and o.object_type = 'Type'

-- Event columns
select
    o.name as [event],
    oc.name as column_name,
    oc.column_type as column_type,
    oc.column_value as column_value,
    oc.description as column_description
from
    sys.dm_xe_packages as p
    inner join sys.dm_xe_objects as o
        on p.guid = o.package_guid
    inner join sys.dm_xe_object_columns as oc
        on o.name = oc.object_name
        and o.package_guid = oc.object_package_guid
where
    (p.capabilities is null or p.capabilities & 1 = 0)
and (o.capabilities is null or o.capabilities & 1 = 0)
and (oc.capabilities is null or oc.capabilities & 1 = 0)
and o.object_type = 'event'
--and o.name like '%lock%'
order by event, column_name


-- Configurable Event Columns
-- These elements are optional and usually not present in event output.
-- They can be enabled as needed.
select
    o.name as [event],
    oc.name as column_name,
    oc.column_type as column_type,
    oc.column_value as column_value,
    oc.description as column_description
from
    sys.dm_xe_packages as p
    inner join sys.dm_xe_objects as o
        on p.guid = o.package_guid
    inner join sys.dm_xe_object_columns as oc
        on o.name = oc.object_name
        and o.package_guid = oc.object_package_guid
where
    (p.capabilities is null or p.capabilities & 1 = 0)
and (o.capabilities is null or o.capabilities & 1 = 0)
and (oc.capabilities is null or oc.capabilities & 1 = 0)
and o.object_type = 'event'
--and o.name = 'file_write_completed'
and oc.column_type = 'customizable'

-- Configurable options
select
    oc.name as column_name,
    oc.column_id,
    oc.type_name,
    oc.capabilities_desc,
    oc.description
from
    sys.dm_xe_packages as p
    inner join sys.dm_xe_objects as o
        on p.guid = o.package_guid
    inner join sys.dm_xe_object_columns as oc
        on o.name = oc.object_name
        and o.package_guid = oc.object_package_guid
where
    (p.capabilities is null or p.capabilities & 1 = 0)
and (o.capabilities is null or o.capabilities & 1 = 0)
and (oc.capabilities is null or oc.capabilities & 1 = 0)
and o.object_type = 'target'
--and o.name = 'file_write_completed'
and oc.column_type = 'customizable'

-- Map Values
select
    name,
    map_key,
    map_value
from sys.dm_xe_map_values
where 1 = 1
--and name = 'wait_types' 
--and map_value like 'lck%'

Answer 2

After doing a bit of digging I have found that the definitions for the sessions are stored in the master database (makes sense when you think about it as the sessions are defined at the server level).

Backing this database up will back up your sessions that you have created, but it isn't the easiest way to restore them. You're probably best either exporting your sessions and saving them as templates, or scripting them out and saving them somewhere safe.