Earlier this week, I posted a blog post SQL SERVER – Identify Table Creation Date from Traces. I received a lot of emails about this blog post and many wanted to know what are the events which are traced in default trace events. Let us see how to see all the list of trace events and their category details.
Run the following query and it will list all the available trace events. I have run the query for SQL Server 2019. It is possible that your version of SQL Server may have different trace events.
SELECT * FROM sys.trace_events
When you run the query above it will return 180 rows in SQL Server. There are three columns, the first one is trace event Id, the second is category id and the third is the name of the event. Here is the sample screenshot.
If you want to know all the categories the trace event has. Here is another query that you can run to get that result.
In SQL Server 2019 there are 21 categories and I have listed all of them here in the image.
SELECT * FROM sys.trace_categories
I personally find trace events very helpful. However, SQL Server official documentation says that we should start using the extended events over traces as they are more developed and comprehensive. While I really like the extended events, I am still a huge fan of traces.
If you have any questions, you can always reach out to me on Twitter.
Reference: Pinal Dave (http://blog.SQLAuthority.com)