Extended Events - Database Usage

-- If the Event Session Exists, drop it first

IF EXISTS (SELECT 1

                  FROM sys.server_event_sessions

                  WHERE name = 'SQL_DatabaseUsage')

      DROP EVENT SESSION [SQL_DatabaseUsage]

      ON SERVER;

-- Create the Event Session

CREATE EVENT SESSION [SQL_DatabaseUsage]

ON SERVER

ADD EVENT sqlserver.lock_acquired(

      WHERE owner_type = 4 -- SharedXactWorkspace

        AND resource_type = 2 -- Database level lock

        AND database_id > 4 -- non system database

        AND sqlserver.is_system = 0 -- must be a user process

)

ADD TARGET package0.asynchronous_bucketizer

( SET slots = 32, -- Adjust based on number of databases in instance

        filtering_event_name='sqlserver.lock_acquired', -- aggregate on the lock_acquired event

        source_type=0, -- event data and not action data

        source='database_id' -- aggregate by the database_id

)

WITH(MAX_DISPATCH_LATENCY =1SECONDS); -- dispatch immediately and don't wait for full buffers

GO

-- Start the Event Session

ALTER EVENT SESSION [SQL_DatabaseUsage]

ON SERVER

STATE = START;

GO

-- Parse the session data to determine the databases being used.

SELECT  slot.value('./@count', 'int') AS [Count] ,

        DB_NAME(slot.query('./value').value('.', 'int')) AS [Database]

FROM

(

      SELECT CAST(target_data AS XML) AS target_data

      FROM sys.dm_xe_session_targets AS t

    INNER JOIN sys.dm_xe_sessions AS s

            ON t.event_session_address = s.address

      WHERE   s.name = 'SQL_DatabaseUsage'

        AND t.target_name = 'asynchronous_bucketizer') AS tgt(target_data)

CROSS APPLY target_data.nodes('/BucketizerTarget/Slot') AS bucket(slot)

ORDER BY slot.value('./@count', 'int') DESC

GO