Recently during a demo at a SQL Saturday the query to pull the Extended Event session data, didn’t return the expected results. The session I used for the demo was the create database statement.
Prior to the session, I deleted the Create Database session, however did not delete the target files because they are part of the demo. Then I recreated the session, just as I had done before. However, this time was there was a difference when I attempted to read the target data. The entry for the newly created database was not showing up when I used the GUI, however was showing up when I read the XML. During the session, I was not able to figure out why that was the case.
When I got home I did some more testing. As I turns out, when I had done the same session the previous week at a different SQL Saturday, I created a filter for the data and that filter persisted, even though I had deleted the session and created it again.
So I decided to do some testing. I made sure there were no longer any filters on the session data, then deleted the Create Database session. Just to have a clean slate, I then restarted the SQL Server service.
Then I created two databases, test4 and test5. As you can see below, they are both showing up when reading the event file using the GUI.
Then I added a filter to only show the test5 database by right clicking on the test5 value and then clicking, “Filter by this value”.
This resulted in only the test5 database showing in the grid.
Now that the session was up and running, I create another database, test6. I restarted the Extended Event session to force the new database event to be written to the target. Then I read the target again. As you can see below, not only is test4 not showing up, but the newly created test6 is also not appearing.
I right clicked the test5 value and then to “Filter by this value”. What I saw kind of surprised me a bit. Below is a screenshot of what I saw, two entries for the test5 value. This means that one had to exist before I tried to filter by the value again.
I clicked the Clear All button, and sure enough, all three databases now appear. Just as in the image below.
What I found interesting is that the filter persists, even after deleting the session and creating it again. Of course, deleting sessions is not a common task and recreating it with the same name and storing the target files in the same location is even less likely to happen. However, I did find this interesting.
Thanks for stopping by my blog!!!!