Extended Events: Database Auditing

Extended Events can do things that simply are not possible with Profiler and another example comes from the stack of audit events that exist only in Extended Events. One of these is a set of expanded events for database auditing.

Comparing the list of things exposed through Extended Events to those exposed through Trace/Profiler isn’t entirely fair. All new functionality is only in Extended Events since Trace hasn’t been updated since 2008. However, these events that you can use to audit your database, aren’t new functionality, they’re just new events for watching old functionality. The addition of new events is just one more reason why moving to use Extended Events is a must.

Auditing Databases

In this instance, when I say audit the database, what I mean is keep an eye on things like database creation, dropping databases, attaching, detaching, starting and stopping. We’re not talking granular behavior here, but rather gross behavior.

Let’s first take a look at what you can see from Trace/Profiler:

• Data File Auto Grow
• Data File Auto Shrink
• Log File Auto Grow
• Log File Auto Shrink
• Object: Altered (databases are objects too)
• Object: Created
• Object: Deleted
• Audit Backup/Restore Event
• Audit Database Management Event
• Audit Database Object Management Event (might not count, but I’m trying to be complete)

I’m not including things like security auditing, change of ownership, stuff like that. Again, what’s happening to the databases on my system is my principal concern here. You frequently see this question online. Who created the database? Who backed it up? Etc. That’s what we’re going for.

I’ve created a Trace Event script to capture all those events. I’m not pasting the code here because it’s just a bunch of numbers. If you want to recreate this, just use the list above.

Here’s the script for the Extended Events I’m capturing. Again, the focus is only on behaviors that existed in 2008 as well as 2019:

CREATE EVENT SESSION [DatabaseBehavior]
ON SERVER
    ADD EVENT sqlserver.bdc_alter_database_collation_request_completed,
    ADD EVENT sqlserver.bdc_alter_database_name_request_completed,
    ADD EVENT sqlserver.bdc_drop_database_request_completed,
    ADD EVENT sqlserver.database_attached,
    ADD EVENT sqlserver.database_cmptlevel_change,
    ADD EVENT sqlserver.database_created,
    ADD EVENT sqlserver.database_detached,
    ADD EVENT sqlserver.database_dropped,
    ADD EVENT sqlserver.database_file_size_change,
    ADD EVENT sqlserver.database_recovery_progress_report,
    ADD EVENT sqlserver.database_recovery_times,
    ADD EVENT sqlserver.database_recovery_trace,
    ADD EVENT sqlserver.database_started,
    ADD EVENT sqlserver.database_stopped,
    ADD EVENT sqlserver.databases_backup_restore_throughput,
    ADD EVENT sqlserver.databases_bulk_copy_rows,
    ADD EVENT sqlserver.databases_bulk_copy_throughput,
    ADD EVENT sqlserver.databases_bulk_insert_rows,
    ADD EVENT sqlserver.databases_bulk_insert_throughput,
    ADD EVENT sqlserver.databases_data_file_size_changed,
    ADD EVENT sqlserver.databases_log_file_size_changed,
    ADD EVENT sqlserver.databases_log_file_used_size_changed,
    ADD EVENT sqlserver.databases_log_growth,
    ADD EVENT sqlserver.databases_log_shrink,
    ADD EVENT sqlserver.databases_log_truncation
    ADD TARGET package0.event_file
    (SET filename = N'DatabaseBehavior');

If you count them up, that’s 25 to 10. However, there’s some interesting behavior that we should note (and yes, it actually favors Trace, a little). Let’s run some tests and I’ll show you.

Testing Database Auditing

We’re going to start simply:

CREATE DATABASE Bob;
GO

DROP DATABASE Bob;
GO

Yeah, I’ve been watching Black Adder again. So, anyway, here’s the output from Trace:

I’m not going to lie to you. That’s more than I expected. I can’t show all the columns here, but the Object:Created and Object:Deleted events captured the begin and commit of the CREATE and DROP statements as did the “Audit Database Management Event.” This is what I mean when I say Trace does more than is immediately apparent.

Now, let’s compare that to the output from Extended Events:

You can see that we’ve got a lot more information here. We get the create and drop events, just like with trace. However, I’m also seeing the recovery step and the database being stopped before it gets dropped.

Let me be very clear here. This is a silly simple example, yet, Extended Events far outshines what’s possible in Trace. And that “databases_log_file_used_size_changed” thing in the middle was in response to changes in the master database because I added the new database, Bob.

More Testing of Database Auditing

Let’s take another example:

ALTER DATABASE Bob SET COMPATIBILITY_LEVEL = 120;
GO
ALTER DATABASE Bob SET COMPATIBILITY_LEVEL = 150;
GO

We get output from Trace:

The Object: Altered event captures that something happened, but, frankly, in this case, it’s useless because it doesn’t capture the statement or tell us what the alteration was. Luckily for trace, “Audit Database Management Event” is there and does in fact let us know the statement so we can see the compatibility level changed.

Here’s the Extended Events output:

I added the attach_activity_id column from causality tracking (something else you can’t do in Trace) in order to show both the internal and external events that occurred. You can see the two compatibility level changes (more on that in a sec) and you can see the log_file changes caused to multiple databases, well, master & Bob, because of these commands. You don’t see this behavior in Trace.

One more thing that you get here that you don’t get in Trace is some detail in the database_cmptlevel_change event:

Note that we can see the previous value and the new value, information that simply isn’t available from Trace.

I can keep going with this, example, after example. We can get into lots of fun stuff too. You should see the database_recovery_trace output when you run restores. Ooh, cool stuff.

You Can’t Do That In Profiler

I’ve said it before and I’ll say again until it sticks. Extended Events is not an upgrade for Trace/Profiler. It’s more. We’re not just looking at another way to capture query behaviors. We’re looking at all new options for monitoring your servers, understanding behavior, and auditing the changes to your databases in a way that was never possible before. Extended Events offers all new functionality, even on old functionality like a RESTORE or a DROP DATABASE command. This is why I’m trying to educate as many people about this as possible. This goes so far beyond which query is running slower today and you need to understand that.

I hope these posts are helpful. I’ve got hundreds of more examples. Let me know in the comments. Also, let me know how you’re using Trace to do things other than capturing query behavior. Maybe there are things it does that we can’t do in Extended Events. I’d love to learn that stuff too.

---

I have a couple of precons coming up, one directly related to Extended Events, and another that’s not:

I’ll be teaching Extended Events as well as Query Store and Execution Plans and a little bit more at SQLIntersection this spring:

SQLIntersection: April 7-9

On the other hand, if you want to talk DevOps, I have an all day class at Bits:

SQLBits: April 1