Tracking database usage with Extended Events returns invalid database_id?

I was recently emailed with a question about tracking database usage information using Extended Events and the person wanted to know why the event session was returning invalid database_id’s as a part of what it was collecting.  The funny thing is that I’ve seen this before during demo’s when I have a very specific concurrent workload running for what I am demonstrating and I used to make sure to do this exact thing so I could explain it during Immersion Events.  The TLDR answer to this is that Extended Events isn’t returning an invalid database_id at the time that the event occurs, the database_id just isn’t valid when the data is being reviewed or consumed.  How could that be you wonder?

DBCC CHECKDB or a manual user snapshot of a database; in either of these two scenarios, the snapshot used internally by DBCC CHECKDB (also CHECKTABLE, and the other CHECK commands) is assigned a database_id just like a regular user snapshot is, so when the event fires the database_id reported for the event is actually correct, but the snapshot is eliminated by the time the data is consumed, so using DB_NAME() on the database_id returns NULL making it appear like the event session is returning invalid data.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.