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.