How can I filter an extended event by database in SQL Server 2008 R2

Question

If I do an extended event session like this in SQL Server 2008 R2:

CREATE EVENT SESSION [Query tracing] ON SERVER 
ADD EVENT sqlserver.rpc_completed 
    (
        ACTION(sqlserver.sql_text)
        WHERE sqlsever.database_name = 'master'

I get an error:

Msg 25706, Level 16, State 8, Line 8
The event attribute or predicate source, "sqlsever.database_name", could not be found.

The same occurs for WHERE sqlsever.database_id = DB_ID('master')

Is there any way to filter by database?

Answer 1

Two things that are the problem.

There is no database_name action in SQL Server 2008 R2 (it was introduced in SQL Server 2012):

select
    action_name = 
        p.name + '.' + o.name
from sys.dm_xe_objects o
inner join sys.dm_xe_packages p
on o.package_guid = p.guid
where o.object_type = 'action'
and o.name like '%database%';

The output from that is:

action_name
-----------
sqlserver.database_id
sqlserver.database_context

But there is sqlserver.database_id. The second problem is that your attempt with database_id didn't work because you have a typo (you have "sqlsever", missing an "r").

Answer 2

The only available solution is to track transactions that are executed under a specific database context, whether you use database_id or database_name.

You can setup an event session using either predicate expressions to attempt to trace all the activity on a database; but if you execute any statement using fully qualified names against it under the context of any other database, then the event will not catch it.

Trying the following demonstrates it. The first SELECT statement will be captured by the event and the second one will not.

CREATE EVENT SESSION [XE1] ON SERVER 
ADD EVENT sqlserver.degree_of_parallelism(
    ACTION ( sqlserver.nt_username
            ,sqlserver.username
            ,sqlserver.server_principal_name
            ,sqlserver.client_app_name
            ,sqlserver.client_hostname
            ,sqlserver.database_name
            ,sqlserver.session_id
            ,sqlserver.sql_text
           )
    WHERE  ( sqlserver.database_id = 5 /* 5 IS OUTPUT OF "SELECT DB_ID('AdventureWorks2008R2')"  */
            AND (   statement_type = ( 1 ) -- SELECT statements
                 OR statement_type = ( 2 )
                 OR statement_type = ( 3 )
                 OR statement_type = ( 4 )
                 OR statement_type = ( 5 )
                )
           )
    )
ADD TARGET package0.event_file(SET filename=N'C:\sql_xe\XE1.xel',max_file_size=(2),max_rollover_files=(1))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=15 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Then...

USE [AdventureWorks2008R2]
SELECT TOP 1 * FROM [HumanResources].[Employee]

USE [master]
SELECT TOP 1 * FROM [AdventureWorks2008R2].[HumanResources].[Employee]

Querying the target data will only display a single record for the second line above.

I would not rely on this predicate expression.