So after some work by the development team they wanted to close down a legacy database, but weren’t sure if there was a stored proc, or some other connections still being used. Checking on-line there’s of course a lot of ways my favourite was Kendra Little’s – 4 Lightweight Ways to Tell if a Database is Used.

After trying the sp_blitzIndex option, I saw we still had a few reads and writes going on for about 10 indexes, so I needed to drill deeper. With that i wanted to move to a XE option and came up with the below. I’ve written 2 versions so it can be ran against 2008 or 2012+ (Not tested on 2016). Hopefully it can help others as it did us.

/*

XE - Database Usage


*/

/* SQL SERVER 2012 */
CREATE EVENT SESSION [DB_Usage] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION ( sqlserver.client_app_name, sqlserver.client_hostname,
    sqlserver.username )
    WHERE ( [sqlserver].[database_name] = N'MyDatabaseName' ) ) -- DEFINE DATABASE TO MONITOR
ADD TARGET package0.event_file (  SET filename = N'C:\Traces\DB_Usage.xel' ,
                                  metadatafile = N'C:\Traces\DB_Usage.xem' );


ALTER EVENT SESSION DB_Usage ON SERVER
STATE = START;
GO



/* SQL SERVER 2008 */
CREATE EVENT SESSION [DB_Usage] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION ( sqlserver.client_app_name, sqlserver.client_hostname,
    sqlserver.username )
    WHERE ( [sqlserver].[database_id] = 9 ) ) -- DEFINE DATABASE TO MONITOR
  ADD TARGET package0.asynchronous_file_target (  SET filename = N'C:\Traces\DB_Usage.xel' ,
                                  metadatafile = N'C:\Traces\DB_Usage.xem' );


ALTER EVENT SESSION DB_Usage ON SERVER
STATE = START;
GO

You can then read the results via the below query (It works for both versions)

/* 

RESULTS QUERY  


*/
;
WITH    event_data
          AS ( SELECT   data = CONVERT(XML, event_data)
               FROM     sys.fn_xe_file_target_read_file('C:\Traces\DB_Usage*.xel',
                                                        'C:\Traces\DB_Usage*.xem',
                                                        NULL, NULL)
             ),
        tabular
          AS ( SELECT   [host] = data.value('(event/action[@name="client_hostname"]/value)[1]',
                                            'nvarchar(4000)') ,
                        [app] = data.value('(event/action[@name="client_app_name"]/value)[1]',
                                           'nvarchar(4000)') ,
                        [date/time] = data.value('(event/@timestamp)[1]',
                                                 'datetime2') ,
                        [username] = data.value('(event/action[@name="username"]/value)[1]',
                                                'nvarchar(4000)') ,
                        [statement] = data.value('(event/data[@name="statement"]/value)[1]',
                                                 'nvarchar(4000)')
               FROM     event_data
             )
    SELECT  [host] ,
            [app] ,
            [username] ,
            [statement] ,
            [date/time]
    FROM    tabular
    ORDER BY [date/time] DESC;




You get results like

DBUSed01

Advertisements