I’ve mentioned before that I’m something of an absentminded DBA – I know where to find information, even if I can’t keep it in my head all the time. One of the lookups I have to do most often is for server side trace / SQL Profiler event numbers and names.
See, when you create a server side trace, you start with a template generated from SQL Profiler (see my article on server side trace). But after that you may want to add or remove events without re generating the whole script; for that, you need the event number.
Also, when I take a trace (on test only!) using SQL Profiler, I save the results to a trace table so I can query through them. You know, SELECT * FROM Trace0708 WHERE TextData LIKE ‘%UPDATE%” and SPID=75, right? But of course, events in the table are displayed as numbers, and I can never remember whether it’s EventID 12 or 13 that’s SQL:BatchCompleted. (It’s 12.)
In SQL Server Books Online, the full chart of all trace events (and columns) is in the sp_trace_setevent article. Here are just a few of the most common events, for quick reference:
| Event # | Event |
| 0-9 | Reserved |
| 10 | RPC:Completed |
| 11 | RPC:Starting |
| 12 | SQL:BatchCompleted |
| 13 | SQL:BatchStarting |
| 14 | Audit Login |
| 15 | Audit Logout |
| 16 | Attention |
| 17 | ExistingConnection |
| 18 | Audit Server Starts and Stops |
| 19 | DTCTransaction |
| 20 | Audit Login Failed |
| 21 | EventLog |
| 22 | ErrorLog |
| 34 | SP:CacheMiss |
| 35 | SP:CacheInsert |
| 36 | SP:CacheRemove |
| 37 | SP:Recompile |
| 38 | SP:CacheHit |
| 39 | Deprecated |
| 40 | SQL:StmtStarting |
| 41 | SQL:StmtCompleted |
| 42 | SP:Starting |
| 43 | SP:Completed |
| 44 | SP:StmtStarting |
| 45 | SP:StmtCompleted |
| 46 | Object:Created |
| 47 | Object:Deleted |
| 59 | Lock:Deadlock Chain |
| 60 | Lock:Escalation |
Happy days,
Jen McCown
Pingback: Tweets that mention SQL Awesomesauce » Blog Archive » SQL Trace Events Reference -- Topsy.com
You can also find the event list in the sys.trace_events table in SQL 2005 and higher. There’s also sys.trace_columns for the trace columns and sys.trace_event_bindings for the available columns for each event.
Excellent, thanks Rob!