SQL Server Extended Events Filtering by Database Name

By: Aaron Bertrand | Updated: 2021-01-14 | Comments | Related: > Extended Events

Problem

In my previous tip about SQL Server Extended Events, I discussed the trade-offs involved with choosing to store the database name instead of looking up the name via database_id later. At the time, we decided to use the latter approach since we are generally not concerned about the edge case where DDL is performed against a database that is then dropped before collection occurs.

Something else that has since come up: we want to filter out activity against a set of databases matching a specific name pattern since the DDL activity there is system-generated and does not need to be audited. We quickly learned that you have to be careful with how you use the database name in an Extended Events predicate.

Solution

Before we look at how to filter the activity, let's cover what our Extended Events session looks like that we are using to collect the data.

Our Extended Events session is pretty simple:

CREATE EVENT SESSION TheDDLSession 
ON SERVER 
ADD EVENT sqlserver.object_altered
(
    ACTION(sqlserver.server_principal_name, sqlserver.sql_text, sqlserver.database_name, ...)
    WHERE ([database_id] <> (2))
)
ADD TARGET ...;

This is filtering out any activity against tempdb.

But, we want to also filter out any activity against a database name with the pattern 'XYZ[_]%' (refer to this tip to understand LIKE syntax). Since we can no longer rely on database_id, the intuitive thing for me was to add a predicate against sqlserver.database_name as follows:

 
    WHERE ([database_id] <> (2)) AND NOT (sqlserver.database_name LIKE N'XYZ[_]%')

Testing some DDL statements, the results were surprising. I expected the predicate to ignore any action against an object in either tempdb or a database named XYZ_ABC. Here I ran ALTER object commands against three different objects; one in a user database, one in tempdb, and one in a user database matching the pattern I wanted to exclude. I ran those three statements from the context of each of the three databases:

USE userdb; -- then from tempdb, then from XYZ_01
GO
ALTER TABLE userdb.dbo.t1 ADD from_userdb int;  -- should be captured
GO
ALTER TABLE tempdb.dbo.t1 ADD from_userdb int;  -- should not be captured
GO
ALTER TABLE XYZ_01.dbo.t1 ADD from_userdb int;  -- should not be captured

What ended up happening in this case:

USE userdb; -- then from tempdb, then from XYZ_01
GO
ALTER TABLE userdb.dbo.t1 ADD from_userdb int;  -- was captured; great!
GO
ALTER TABLE tempdb.dbo.t1 ADD from_userdb int;  -- was not captured; great!
GO
ALTER TABLE XYZ_01.dbo.t1 ADD from_userdb int;  -- was captured; wrong!

I wondered what was going wrong. I realized (and confirmed with my pal Jonathan Kehayias) that my predicate should use the action's database_name, not sqlserver.database_name from the event. So, I changed the session again, to:

ADD EVENT sqlserver.object_altered
(
    ACTION(sqlserver.server_principal_name, sqlserver.sql_text, sqlserver.database_name, ...)
    WHERE ([database_id] <> (2)) AND NOT (database_name LIKE N'XYZ[_]%')
)

Then, I ran the above commands (but am focusing here on what happens when the target object is in database XYZ_01):

USE userdb; 
ALTER TABLE XYZ_01.dbo.t1 ADD from_userdb int;  -- was captured; wrong!
GO
USE tempdb; 
ALTER TABLE XYZ_01.dbo.t1 ADD from_tempdb int;  -- was captured; wrong!
GO
USE XYZ_01; 
ALTER TABLE XYZ_01.dbo.t1 ADD from_xyz_01 int;  -- was captured; wrong!

I went back and realized why this happened: database_name the action is an empty string when you haven't explicitly collected the database name. An empty string is definitely NOT LIKE N'XYZ[_]%', so as a result it wasn't filtered out. I changed the session again:

ADD EVENT sqlserver.object_altered
(
    SET collect_database_name = (1)
    ACTION(sqlserver.server_principal_name, sqlserver.sql_text, sqlserver.database_name, ...)
    WHERE ([database_id] <> (2)) AND NOT (database_name LIKE N'XYZ[_]%')
)

And voila:

USE userdb; 
ALTER TABLE XYZ_01.dbo.t1 ADD from_userdb int;  -- was not captured; great!
GO
USE tempdb; 
ALTER TABLE XYZ_01.dbo.t1 ADD from_tempdb int;  -- was not captured; great!
GO 
USE XYZ_01; 
ALTER TABLE XYZ_01.dbo.t1 ADD from_xyz_01 int;  -- was not captured; great!

I rounded out the testing with a session that collected the database name but still used sqlserver.database_name; it behaved incorrectly, as expected.

The total testing matrix looks like this:

You can see that tempdb was always filtered out correctly. It relies on database_id, which doesn't reflect the context database of the session. You can also see that the context database led to missing events in some cases and collecting too many events in others.

Next Steps

The moral of the story is, if you want to filter on a database name or pattern, you need both SET collect_database_name = (1) and use database_name in your predicate (not sqlserver.database_name). If you don't do both, you won't be collecting the right events in all scenarios.

See these related tips and other resources related to Extended Events:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-01-14

SQL Server Extended Events Filtering by Database Name

Problem

Solution

Next Steps

About the author

Comments For This Article