Extended Events File Initialization Failure

It should come as no surprise that I write a lot of articles about Extended Events (XE). This happens to be another article on Extended Events. Truth be told, this article is hopefully something that is more of an edge case scenario. Well, I sure hope that is the case and that it is not a common problem.

One of the recommended methods to trap payload data in an XE session is via the use of the event_file target. Sending data to a file has numerous benefits such as being able to take the trace and evaluate the trace file from a different machine (locally to that machine).

Every once in a blue moon you just may run into various issues with the event_file such as explained here or here. Though slightly different, the net effect is quite similar and should be treated with roughly the same kind of troubleshooting steps.

Configuration Error

As luck would have it, I ran into one of these rare opportunities to troubleshoot an error occurring on a client server. Truth be told, I was unfamiliar with the actual error at first. Here is that error.

Error: 25602, Severity: 17, State: 22.

The preceding error was scraped out of the SQL Server error log. Obviously a little more detail was needed because this error is far from useful without that detail. Looking a little deeper, I found some errors like this.

Msg 25602, Level 17, State 22, Line 43
The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”,
encountered a configuration error during initialization. Object cannot be added to the event session.

Some very good clues are actually contained in that particular message. Some of these clues include the following: a) the term “target”, b) the term “event_file”, and c) the phrase “event session.” Ok, I get it at this point. One of my Extended Event Sessions I had put on the server and used previously was broken. But, since it had been working and I know I had fetched data from it, I found myself puzzled as to why it might be busted.

The next logical thing to do at this point was to test the various sessions that are stopped and try to figure out which one is causing the problem and see if the error is reproduced. Finally upon finding the session that is failing, I ran into the complete message.

Msg 25602, Level 17, State 22, Line 43
The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”,
encountered a configuration error during initialization. Object cannot be added to the event session.
The operating system returned error 3: ‘The system cannot find the path specified.
‘ while creating the file ‘C:\Database\XEDROPME\SVRLoginAudit_0_131650006658030000.xel’.

The additional info that I needed is in bold text in the previous text. So, for some reason, there is a problem with the path for the XE trace file output. Going out to the file system to check it out, I found that the client in this case decided to delete the entire folder. How does that happen? Well, it does! When it happens, the XE traces will start to fail and you will no longer capture the intended trace data. Let’s take a look at a simulated reproduction of this issue.

First, I will create a session and then start the session, then validate the session exists and then stop the session.

And here is what I see on my test server when I validate the session exists.

Perfect so far. Now, let’s make that trace output directory disappear. For this demo, you might note that I had created the directory as “C:\Database\XEDROPME”. My intent in the name was obviously to notify the world that the folder was to be dropped.

That statement is easy enough and is performed from my test environment for those getting weary of the use of xp_cmdshell. Now, let’s try to start that session that we knew was previously running.

I re-formatted the output of the error for ease of readability. Otherwise, the output in the preceding image is what will happen when the output directory is dropped. The fix is rather simple at this point – put the folder structure back into place. To read an introductory post about checking if a session exists or not on your server, check this out (a more advanced post is coming soon).


From time to time we will run into various problems supporting Extended Events. This is bound to happen more frequently as we support more varied environments with more hands in the kitchen (so to speak). We need to learn that even small changes can have a ripple effect to other things that may be running on the server. It is worthwhile to perform a little due diligence and clean things up as we make changes – or at minimum to observe the system for a time to ensure no unintended consequences have occurred.

Extended Events is a powerful tool to help in troubleshooting and tuning your environment. I recommend investing a little time in reading the 60 day series about Extended Events. This is not a short series but is designed to provide an array of topics to help learn the tool over time.

Profiler for Extended Events: Quick Settings

Not long ago, I wrote a rather long article about a new-ish feature within SQL Server Management Studio (SSMS) that impacted Extended Events. You can read that book – here! The XEvents Profiler feature is one of those things that you may or may not use. If you consider using the feature, I do believe it is important that you research it a bit and try to learn the pros and cons first.

With that there is a little more about the feature that the aforementioned book did not cover. In fact, this information has pretty much gone ignored and mostly stays hidden under the covers.


As of SSMS 17.4 we have been given the ability to control XEvents Profiler just a tiny bit more. For what it is worth, we as Database Professionals love to be able to control our database environment. So this teeny tiny bit of new control ability is potentially a huge win, right?

If you are the controlling type, or maybe just the curious type, you will be pleased to know that under “Options” from the Tools menu in SSMS, Microsoft has tucked some new control options to help you configure XEvents Profiler – to a degree. If you open options, you will see this new node.

If you expand the “XEvent Profiler” node (circled in red), you will discover the “options” node. If you click on this “options” node and do a quick comparison (in SSMS 17.4 and SSMS 17.5) you will also find that you don’t need t expand the “XEvent Profiler” node at all because the options are listed in the right hand pane for both nodes and they are exactly the same. So, choose one or the other and you will end up at the same place.

The options that you currently have are:

  • Stop Session on Viewer Closed
  • Toolbar commands stop and restart

You can either set these options to True or False. I recommend you play with them a bit to discover which you really prefer. That said, I do prefer to have the “Stop Session on Viewer Closed” set to true. There is “profiler” in the name of the feature afterall. And if you have read the “book” I wrote about this feature, you would know that the filtering offered by the default sessions of this feature basically turn on the fire hose effect and can have a negative impact on your server. Are you sure you want a profiler style fire hose running on your production server?


There surely will continue to be more development around this idea of an XE style profiler. More development generally means that the product will mature and get better over time. This article shows how there is more being added to the feature to try and give you better control over the tool. We love control so the addition of these options is actually a good thing. Is it enough to sway me away from using the already established, more mature, and high performing tools that have been there for several generations? Nope! I will continue to use TSQL and the GUI tools available for XE that predated the XEvent Profiler.

Some say this is a way of bridging the gap. In my opinion, that gap was already bridged with the GUI that has been available for several years. Some say that maybe this tool needs to integrate a way to shred XML faster. To that, I say there are methods already available for that such as Powershell, the live data viewer, the Target Data viewer, or even my tools I have provided in the 60 day series.

I would challenge those that are still unfamiliar with the XE GUI (out for nearly 6 years now) to go and read some of my articles or articles by Jonathan Kehayias about the power that is in XE as well as some of the power in the GUI.

Checking the Health of your CLR

Comments: 1 Comment
Published on: January 16, 2018

The Common Language Runtime (CLR) is the fundamental nerve center of the Microsoft .NET Framework. It is this nerve center that provides the execution environment for all .NET Framework code. One may sometimes refer to CLR code as managed code.

CLR hosted from within SQL Server (CLR Integration), provides the ability to create stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates. And because managed code compiles to native code, we sometimes see a bit of a performance gain for various scenarios.

It is this bit of a performance gain, that we may see more CLR integration use within our SQL environments. And with increased CLR there will be an increased chance for something to go a little haywire. If things can (and will go haywire), it is important to have a means to be able to monitor them.

If you know me, you are probably aware that when it comes to monitoring a problem, I will most probably recommend a solution that involves a little bit of XE. If you don’t know me, here is a hint: some of those monitoring tools can be found in this series.


I had a client reach out recently because they were having all sorts of issues with their CLR procs. The client is losing some sleep, a little weight, and a fair amount of hair trying to figure out what is causing their CLR nightmares. This client has magic CLR. The CLR works fabulous or a day or three. Then suddenly the CLR procs just disappear and have to be re-created on the server. Granted this seems more like a problem of somebody did something they didn’t realize they were doing and poof the CLR is gone.

So, what can we do about this? The answer is quite simple. I am going to use an extended event session that will monitor the various aspects of CLR from a couple of different angles. First, I need to try and find events that fit my needs. I can do that with the queries and instructions found here.

As I query the XEvent catalog, I can see that there is a limited set of options and they can be seen in the following image.

Most of the events can be found in the sqlclr package and in the debug channel. The exception in both cases being the “assembly_load” event. I will go ahead and add all of those events to my session except for the garbage collection event.

The next step is to help cover my bases and see if somebody might be changing the objects and causing the odd behavior. I will do that with the object_created, object_altered, and object_deleted events. The caveat here is I will limit the scope of those events to only look for CLR specific changes. I will be able to do that by filtering on object_type in each of those three events. Here is a look at those object types.


In Extended Events, we see that we can monitor for object changes in the various different objects such as “TRIGASM”,”PROCASM”, and “FNTABASM”. What do these map to though? Well, here is the answer to that question!

  • 16724 TRIGASM = Assembly (CLR) DML trigger
  • 17232 PROCASM = Assembly (CLR) stored-procedure
  • 21313 ASM = CLR Assembly
  • 21318 FNSCLASM = Assembly (CLR) scalar-function
  • 21574 FNTABASM = Assembly (CLR) table-valued function
  • 17985 AGG — Assembl Aggregate function (CLR)

With all of this rolled together, we can now assemble the XE Session.

Once deployed, I can go ahead and follow the instructions here to test different CLR managed code objects.

The Wrap

Having a tool to be able to monitor CLR health will be essential as you deploy more and more managed code within SQL Server. Extended Events offers a great lightweight means to do just that. This article has shown how to deploy a session that will capture the various changes with CLR objects within our database environment. In addition, you will be able to capture various conditions related to performance or problems with the managed code. You may even recognize some of the CLR events from the system_health session.

If you are interested in learning more about Extended Events, I recommend you read my “60 day” series of articles on Extended Events. The series continues to grow and covers a pretty decent depth and breadth on the topic.

Sharepoint Diagnostics and XE

Comments: 2 Comments
Published on: January 3, 2018

One of the all-time greatest and most beloved applications among DBAs happens to be Sharepoint. Most of us would be lying if we said that we loved Sharepoint and the kind of performance issues it can cause on a SQL Server.

When you have an application that comes with a notoriety of causing performance pain, you will want to have some tools or find some tools that can help you find the pain points. Lucky for the savvy DBA, there are tools to be able to capture this kind of information. Hint: some of those tools can be found in this series.


One of the best tools for capturing performance impacting data is Extended Events. Extended Events is simply a tool that everybody needs to learn. And in this case, it provides an opportunity to support and troubleshoot Sharepoint as well as SQL Server.

Truth be told, the default XE session for Sharepoint does not come installed when you install Sharepoint. But, there is a session that gets installed when you configure SPDiag for your Sharepoint needs. I am not going to dive deeply into that tool or the Sharepoint tooling. That would be well outside the use and scope of this post. Here is the default session created by that tool.

As you can see, there is not much special about the session. This is a basic session to capture statements queries executed against the server. In fact, you will probably note it is similar to both of the default XE Profiler sessions I discussed here. The first caveat here is the application filter for Sharepoint apps. That said, if you are running multiple Sharepoint sites and configure SPDiag on both, and both share the same backend database server, guess what happens? Yup, you guessed it – you get two Sharepoint sessions that capture the exact same data.

The second caveat here is a fun thing about the service that gets created to support the Sharepoint XE Sessions. The SPDiag will alter each of your XE (sharepoint related) sessions many times every few seconds. Every alter of the sessions will be to either stop or restart the session. This seems rather unnecessary and huge miss. There are not many misses with XE, but there can be a misuse of XE similar to all of the abuses of profiler and this is one of those abuses in my opinion. You can easily discover the constant changes to these sessions via an XE session that I will be sharing in a future post. I would recommend you stay tuned for that article and also watch the 60 Day Series for an update that includes that post.

The next caveat here is that this particular session is only configured to go to the ring_buffer. What is the problem there? Well, the ring_buffer means the trace data is volatile and you can easily lose it. In addition, this particular session has been known to cause some memory issues on many installations. You can check for a memory issue by watching for a growing MEMORYCLERK_XE memory count. You can watch it with a query like the following.

In my opinion, I see no good value in running the session as currently built. I would just rather build a better XE session and leave it running instead of constantly stopping and starting the XE session.

The Wrap

In this article I have introduced you to a quick session setup that comes from using the SPDiag tool that could plausibly be useful in the troubleshooting of various different problems (most probably performance related) with Sharepoint. This assuredly is in the absence of a better alternative such as a custom session that you write yourself.

If we browse the data made available to us, we can see that there is adequate info to help us convert all of those SQL Traces to the high-performing XE versions that we should be using these days. I encourage you to browse the capabilities of XE as you work towards converting your profiler based traces to the much friendlier and more powerful counterparts.

If you are interested in learning more about Extended Events, I recommend you read my “60 day” series of articles on Extended Events. The series continues to grow and covers a pretty decent depth and breadth on the topic.

Dynamics AX Event Session

Many moons ago, I wrote about how to enable the context info for Dynamics AX. Having the context info enabled is a pretty important thing when dealing with Dynamics AX. The amount of time you can save yourself and the DAX Admins is astounding – if you have the context info enabled. If you are curious, read more about that here.

It is not an uncommon thing to need to know who is causing performance problems within AX. In addition, you will probably want to know what they are doing. But since that information is obfuscated due to the default setup of AX, you can find yourself beating your head against the desk in frustration.

Once you have the context info enabled, however, how are you going to trap that information? How are you going to figure out what queries are related to the context info passed from AX to the database? In addition, how are you going to parse that context info into something useful? Hint: some of those answers can be found in the aforementioned article – here.


While the previous article goes into a good amount of detail in answering some of those questions, it does not divulge what to do to capture the context info. Capturing the context info is rather easy to do – if you choose to use Extended Events (as I prefer to do). And since this is the giving season, why not just give the session details and then let you play with the shiny new trace for a few days?

All in all, this is not a tremendously difficult session. The session is looking for statement completed events. When there is a qualifying event that fires, then I attach the additional payload data from the several actions (e.g. sql_text, database_id, server_principal_name and context_info) to the event payload so I can query it later.

Will I really need all of those actions? Not necessarily but the data can be useful here or there – especially if you are trying to familiarize yourself with a new DAX environment.

The Wrap

There are many many uses for Extended Events. In this article I show a quick session setup that can be useful in the troubleshooting of various different problems (most probably performance related) with Dynamics AX.

If we browse the data made available to us, we can see that there is adequate info to help us convert all of those SQL Traces to the high-performing XE versions that we should be using these days. I encourage you to browse the capabilities of XE as you work towards converting your profiler based traces to the much friendlier and more powerful counterparts.

If you are interested in learning more about Extended Events, I recommend you read my “60 day” series of articles on Extended Events. The series continues to grow and covers a pretty decent depth and breadth on the topic.

Correlate SQL Trace and Actions

Comments: No Comments
Published on: January 1, 2018

I have recently written about the pains of correlating SQL Trace events to Extended Events (XE) events. That article can be found here. At that time, I did not dive into another facet of trace that truly needs converting as well. That component is known as “Actions” in the world of XE.

In the world of profiler and SQL Trace, these actions were an actual part of the payload for the Trace event. In XE, these actions are more like add-ons. I go into further depth about what an action in XE is – here. I recommend reading that article. Due to that, I won’t be diving into great detail here about what an Action is.


That said, there is one thing in regards to actions in XE that I think would be helpful in translating Traces to XE. In XE, we sometimes see an action referenced as a global field. This is in stark contrast to what the Trace equivalent would be – a local field. Rough translation is that the actions in Trace were localized to every event that needed that particular data. In XE, the field has become more like a function and can be applied across many different events without being tied to every event specifically. Thus – it is renamed to “global field” in many different places.

Knowing that we migrate from localized fields to global fields is helpful in trying to map the old world of SQL Trace to the new and improved world of XE. In addition to that, we have some objects to introduce that will help us translate. Those objects are:

  • sys.trace_event_bindings – a catalog view introduced in 2005 that contains a list of all possible usage combinations of events and columns.
  • sys.trace_columns – a catalog view introduced in 2008 which contains a list of all trace event columns.
  • sys.trace_xe_action_map – a table that contains a row for each XE action that is mapped to a SQL Trace Column.

I will use the data from these objects to map to the XE actions – with a few caveats. In order to map everything properly, each of these objects is required because they have just a tiny bit of the needed information in order to eventually get to the big picture. When I bring it all together, I will have a script something like this.

You may have noticed here that I am excluding some results. I am not interested in anything that does not map to an action in the world of XE. So I am chucking those empty action results to the side. In addition, I am tossing anything that is Audit related. If it is Audit related, I really need to be using the SQL Audit feature (though it is built fundamentally on XE) instead of XE. So anything that is SQL Audit related just needs to be discarded from these results.

This will yield a whopping 2145 actions that are mapped between the local SQL Trace fields and the global XE fields called actions.

If I contrast that to a count of available actions in XE, you will likely see that there is a significant difference and that a lot of inefficiency has been removed.

Depending on version of SQL Server you will get a slightly different result. Here is a quick cheat sheet I worked up for different versions.

As you can see from the image, none of the versions of SQL Server with XE exceed 55 actions (global fields). Compare that to over 2000 results in the old trace world with only 180 events, and I think you would agree it was highly inefficient.

The Wrap

If we browse the data made available to us, we can see that there is adequate info to help us convert all of those SQL Traces to the high-performing XE versions that we should be using these days. I encourage you to browse the capabilities of XE as you work towards converting your profiler based traces to the much friendlier and more powerful counterparts.

If you are interested in learning more about Extended Events, I recommend you read my “60 day” series of articles on Extended Events. The series continues to grow and covers a pretty decent depth and breadth on the topic.

An Introduction to Templates

Comments: No Comments
Published on: December 30, 2017

Templates are a powerful tool in so many trades and crafts. From decals and stickers all the way up to the largest cruise ships in the world, templates can be found everywhere in just about everything that do.

In SQL Server, templates are readily available for your use in so many different ways that I am sure we are unaware of most of them.


A good example of a template in SQL Server, that we clamor about frequently, is the unattended install. I am sure you have probably used that kind of template as you work to save yourself some hair and brain cells trying to remember the exact setup needed for your environment. With an unattended install, you build it once and then use the script to build all future installs.

Even though we may not call the unattended install script a template, it is. Think about the definition(s) of a template for a minute:

  1. something that serves as a model for others to copy.
  2. a preset format for a document or file, used so that the format does not have to be recreated each time it is used.

Looking at this definition, we can easily see that the unattended install routine fits perfectly as a “template”. This is just an example of the availability of templates within SQL Server. There are many different kinds of templates – many we probably don’t ever think about as being “templates” and many that we just flat out don’t bother to think about at all.

I have one specific kind of template in mind that I want to introduce and discuss. This template type comes with Extended Events. I actually introduced the concept very briefly in a recent post about the XE Profiler feature. That article is just one of many articles about Extended Events that I have written. You are welcome to catch up on the series of articles here.


Extended Events has had templates available for quite some time. If you recall from the article about XE Profiler, templates were not introduced as a part of the XE Profiler feature – the templates were introduced quite some time prior to that.

There are really three distinct areas of focus for templates that I would like to discuss. I have them somewhat illustrated in this next screenshot.

In this image, you will see that I have three color coded regions. In green, I have the option to create a template from file. In blue, I have the XE specific templates that have no correlation to profiler. And then in red I have the templates that were created to simulate the behavior of the Profiler based templates while providing some sort of comfort zone for the profiler fanatics out there.

Today, I will focus on the templates that are readily available (red and blue zones). In a future article, I will cover the creation of a custom template (green zone). That said, this is not going to be a traditional article with the technical geekery showing the ins and outs of all of those templates. Oh no, this article will be an entirely different flavor of geekery. Today, I will be focusing on something that is more about fun. I will be exploring the available templates strictly via script (no XE GUI).

Exploratory Surgery

If you are like me, you sometimes just don’t care to pick up the mouse to browse the file system or even browse the SSMS hierarchy to find the exact screen that has some specific information that you want. For me, I ran into this when thinking about the templates in XE. What templates are there in XE again? Crap, now I have to open the GUI to figure that out. I wanted a quick way to be able to look at all of the templates – and not just the templates available to me in the version of SSMS that I am using, I want to see everything out there on the system.

The question is how to do this. The templates are not listed in any system table or view that I could find. The list of templates is specific to the version of SSMS that you are using and the templates are all xml files stored in a directory on the OS. A thought occurred to me while contemplating this dilemma – can I maybe use the registry?

The registry does give me some options so maybe, I can use xp_regread. I won’t be able to use xp_instance_regread since this is not a registry key that appears at the instance level, rather it is an SSMS related reg string. One problem that comes to mind is that I would need to find the SQLPath for each of the versions of SSMS installed (red arrows indicate each of the registry strings I might have to query). I start to think to myself after realizing this that I do not like the looks of this option – but it could be done.

I then thought of another option before going too far down the registry route. When I install an application, that application may modify my “Path” environment variable for the OS. This sounds a little promising. I still have some of the drawback with multiple versions of SSMS installed – but I think I can manage that more easily from the “Path” environment variable.

Let’s take a crack at a script.

Ouch, my eyes hurt already! What have I done here? I have written multiple loops. Never fear! Loops are not all evil. Sometimes, it makes sense to use a looping mechanism. This is one of those times where it makes some sense. This script is a limited use script for the eyes of the DBA only.

I can hear the moans about the use of xp_cmdshell and xp_dirtree and so forth. I am not listening to those grumbles. Again, this script is for fun and on my machine. The discussion on securing the server and xp_cmdshell is a discussion for another time.

In this script, I use the path variable to determine all of the flavors of SSMS that have been installed. Since the path for SSMS is all the way to the BINN folder, I need to do a little replace to get the correct paths for the xe templates. I also added a bit of a union in there because I had to duplicate the results – sorta. You see there are templates for AzureDB that, depending on your Azure Subscription or lack thereof, you may never see. I wanted to include those in my result set!

Looking at these results on a machine with SSMS 14 and SSMS 17, I would see something like the following.

I highlighted a few different sections of the results to help quickly show some of the templates available between different SSMS versions as well as those for Azure DB versus a local instance of SQL Server. Things are a tad different if we have SSMS 16 installed. Here is a sample result from a machine with SSMS 16 installed.

The main point of interest here (at least for me) is the removal of a template from SSMS 16 to SSMS 17. This is probably a template you don’t necessarily want to run on a production system and that is likely why it is gone from the available templates that get installed.

The Wrap

I am sure you can probably find an alternate routine to query these templates via TSQL that may be more efficient. I would be interested to hear about it.

What’s next now that I can get these templates? Well, a thought occurred to me to use this type of routine to create a means to use TSQL to read one of those template files to create the XE session from pure TSQL statements and eliminate the need to use the GUI. I think that would be a lot of TSQL/XE Geekery based fun.

Correlate Trace and XE Events

Comments: No Comments
Published on: December 28, 2017

Several years back, when Extended Events was brand new, it was downright painful to try and convert the classic Profiler or Server Side trace to something meaningful and useful within Extended Events.

That was years ago and really is ancient history now. Microsoft has provided a couple of maps to help us translate the petroglyphs of Profiler into the high tech, smooth running, efficient engine and language of XEvents. This article is going to demonstrate a quick correlation between events in these two tools.


If you have browsed XEvents to any extent you should probably be familiar with at least one map object that Microsoft has given us. That particular object is sys.dm_xe_map_values and I wrote a bit about it here. That object presents some good information about various data values within XEvents that are delivered with the Event payload as well as some hidden gems that can be extra useful for exploring SQL Server internals. The point is, maps is not necessarily a new concept.

While the concept of a map is not new within XEvents, the implementation in this case is a tad different. You see, the dm_xe_map_values object is more like an EAV object while the map I will introduce today is more of an ordinary lookup table. Let’s take a look.

The map I want to concentrate on for now is the sys.trace_xe_event_map table. You read that correctly – it is a table. We don’t have very many “tables” when dealing with XEvents, but this one happens to be. As Microsoft Docs describes it, the table “contains one row for each Extended Events event that is mapped to a SQL Trace event class.

And to show that it is indeed a table, you can run a check like the following.

So this table contains a mapping between Extended Event events and SQL Trace events. If we are curious to see that mapping, we can do a simple query and see the trace_event_id and the XE event name along with the XE package that is related to that specific event. That said, most of us do not have the trace_event_id memorized to human friendly terms so we need to do a little bit more. That is fine because it is possible for us to also see the relationship between those trace ids and the trace event names. Let’s get a little more creative with the query.

Before doing that though, I do want to lay out some  baseline info. There are 180 trace events and of those there are 139 mappings in the map table between SQL trace and XE. This tells us immediately that there is not a one-to-one mapping. What this does not divulge just yet is that there may be a many-to-one mapping (ok, there is a single many-to-one mapping). We will look a bit at the differences in mappings.

And the results will look something like the following.

In the previous image, I highlighted three areas in different colors. First, note the red box surrounding the row count output in my results. Recall that I said there were only 180 events and only 139 rows in the map table. Clearly, we have a map that has a many-to-one relationship.

Next up is the blue box. The only thing we can see here is  that there is a trace event with no mapping to an Extended Event. We expected this result given there were only 139 maps. Of interest here is that most of the unmapped events are Audit related. While SQL Audit relies heavily on the XE Engine, the events from Trace do not map directly to events in XEvents.

The last call-out is the green box. This is there to show the clear mapping between trace events and XEvents events. For the most part, this has not changed clear through SQL Server 2017. If you will notice, I have included the product version, dll file name and the dll version info. That version info helps to underscore what has changed with SQL Trace which is practically nothing.

This brings us to the question on everybody’s mind: What is the many-to-one map? That is an easy answer but the explanation is not quite as easy.

Running that query, I will find that event_id 165 has more than one mapping in the map table. Let’s see what that translates to in human terms.

And this is what it looks like:

So, “Performance Statistics” happens to be the event that maps to multiple different XEvent events. That said, you may wonder why “query_pre_execution_showplan” doesn’t map to something like “Showplan XML” or maybe “Showplan All” which produce the execution plan in similar fashion. I will leave that mystery to you as your deep dive homework assignment.

The Wrap

If we browse the data made available to us, we can see that there is adequate info to help us convert all of those SQL Traces to the high-performing XE versions that we should be using these days. I encourage you to browse the capabilities of XE as you work towards converting your profiler based traces to the much friendlier and more powerful counterparts.

If you are interested in learning more about Extended Events, I recommend you read my “60 day” series of articles on Extended Events. The series continues to grow and covers a pretty decent depth and breadth on the topic.

Message in a Bottle of XE

Comments: 1 Comment
Published on: December 27, 2017

One of the age old features that most probably take for granted in SQL Server happens to be the error messages. What? How can a message be a feature, right?

Well, I concede. Maybe calling it a feature may be too much. Then again, consider it for just a touch longer. Traditionally, SQL Server stores all of the messages pertinent to SQL Server and you can see all of them view the sys.messages catalog view. In addition to that, you can also add custom messages for your specific liking and environment. That last piece is a somewhat important concept in regards to calling “messages” a feature.

So, considering this “feature” for everything that is SQL Server related, does this also mean that all Extended Events related messages are accessible in sys.messages too? Afterall, Extended Events is a SQL Server feature too, right? And, we do occasionally see errors and warning messages in relation to Extended Events.


Let’s go ahead and try to test the theory that XE related messages are accessible to view inside of SQL Server. The first step will be to try and find those messages in the traditional location – sys.messages.

Let’s start with a sample session that will throw an error due to an invalid disk path (I don’t have a Z drive).

If I try to execute that script, I will receive the following message.

Msg 25641, Level 16, State 0, Line 8
For target, “package0.event_file”, the parameter “filename” passed is invalid. Target parameter at index 0 is invalid

Let’s take note of the message number – 25641. I will use that for exploration in the next step. With the message text and ID in hand, I can try and query the sys.messages catalog view to try and determine if this XE related message can be found there.

Running the preceding query will yield the following result.

Obviously that message text is different than what we saw in SSMS when trying to create the invalid event session. The difference in the text can be partly explained away due to the parameters. That said, there is an extra part of the error message that is not showing in sys.messages. Is this the correct message? Did XE do something to maybe append an additional message to this one from sys.messages? Let’s find out.

XE Messages

If I run this next query, I discover something that may be a bit unexpected.

I have discussed most of the other types in my 60 day series, but the message type has been neglected by me so far. That is a neglect I am looking to rectify right here. Let’s see what this message type might actually hold for us. Let’s get a little fancier with this next query to try and discover what we have in the message “type” in XE.

And a sample output from that:

As luck would have it, there are 82 messages for XE that are registered as “objects” within XE in SQL Server 2014 (and 84 in SQL Server 2017). That said, none of them have a message id tied to the message within the XE views or DMVs. So, if I need to try and correlate messages between the two sources, it becomes a little hairier. Now seems like a good time to try this hairy approach.

Scary Hair

Ok, so the query is not actually all that scary. That said, it is not likely to yield very many results due to the issues I mentioned already – the messages don’t entirely match up. So, what can I do instead to try and find these messages? Well, let’s change things up a bit and work off the presumption that the XE engine has added a message to the message 25641 and what we have is two different messages instead of just one. We already have the message for 25641. If we take the difference in that message with what was written to the screen in SSMS, we can do a bit of a search for that term.

Let’s try the following now.

This query is actually two queries. There is one to check the sys.messages view as well as a query to check the messages available via Extended Events. Instead of checking for the full text of the message, I changed it to just a few key words. As it turns out, there are few results that match real well the messages I received. Here are my results.

There we have both of the messages that comprise that single error message received when trying to create that event session with an incorrect file path.

Wrapping up

There we have it, Extended Events will throw a message that does contain messages from sys.messages as well as some additional custom messages in the XE metadata. Unfortunately, there is no way that I have been able to find to create custom XE messages. Quite frankly, I really don’t see a very good reason to create a custom XE message either.

There is a world of depth and knowledge within Extended Events. There is plenty to learn and plenty of ability to monitor and investigate the SQL Servers under our purview. I recommend you take a moment to dive deeper into this great feature of Extended Events.

Extended Events Permissions

Comments: 1 Comment
Published on: December 25, 2017

A common question that I am asked about Extended Events relates to what kind of permissions are required in order to use this awesome feature. In this article, I am going to compare and contrast the permissions required for Profiler as well as Extended Events.

Extended Events changed a lot with how to monitor your server. One of those changes is a slightly different set of permissions to be able to perform various different tasks. Before I dive into what the security landscape is with XEvents, let’s take a peek at what we have to do in order to be able to use Profiler.


If you try to launch a profiler session as a standard login with what should be a typical user type account with restricted access, you will encounter something like the following.

Yes, according to the message you need to be a sysadmin (nobody reads past that first part so they always miss the alternative). So, let’s test things out. I will create a login called TraceUser and attempt to do tasks related to running a Profiler or Server Side type of trace. I will also use this same login when testing the permissions for Extended Events.

I included a quick test script after creating the login to confirm that the user does indeed have restricted access. The test script will produce the following message.

Msg 8189, Level 14, State 6, Line 8
You do not have permission to run ‘SYS.TRACES’.

This is the same net result as if I were to attempt to run a Profiler session – it’s just faster to get to the result. As we can see, the TraceUser login does not have the requisite permission to do anything with traces (profiler or server side).

Now, I will take this just a step further and try to add permissions to validate the minimum permissions needed.

Running this test script now will yield a result of all traces that are currently setup on the server. So, in my case that would be just a result showing the Default Trace and nothing else since I have not put any other traces on the server. I can also confirm the exact permissions granted to TraceUser do not actually exceed what I just granted.

Or is it?

After running that in the master database I will see that granting the “Alter Trace” permission has an extra side effect of granting “Showplan” to the user.


For Extended Events, we have something a little different and a little more (just a little) granular.

Wow! That is quite the permissions escalation, right? Well, it’s not significantly different in some regards to what we see with Profiler. Taking it a step further, XEvents is more in tune with a lot more internals for SQL Server than Profiler. Little things like looking at DMVs or getting the current status for things like Hekaton or Availability Groups, which tie deeper into the engine, will require more permissions. That said, view server state or even these permissions levels is not the end of the world. There are work arounds to the permissions issues. Before I give the work arounds, lets look at the permissions.

Using the TraceUser login I created earlier in this article, I am going to start testing XEvent permissions.

The first step I took in this script was to revoke the “Alter Trace” permission. Then I queried both sys.traces and sys.dm_xe_objects along with running my permissions checks. The attempt to query the two views produces the following messages.

Msg 8189, Level 14, State 6, Line 34
You do not have permission to run ‘SYS.TRACES’.
Msg 300, Level 14, State 1, Line 36
VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
Msg 297, Level 16, State 1, Line 36
The user does not have permission to perform this action.

From the permissions checks, I can also confirm that this login has barely the ability to connect to SQL Server.

I will now grant permissions little by little to see what happens.

Here is the sum of the differences.

The “Alter ..Event Session” permissions should be expected since that is what I explicitly added. That said, the permission, while enough to create an event session does not yield enough permissions to do anything with viewing the session data or looking into the dmvs or catalog views. In fact, if you have a recent enough version of SSMS, the XE Profiler feature will cause SSMS to crash if you try to “launch” a session via the Profiler GUI with only the “Alter Any Event” permission.

That said, I can run the following script to create a new Event Session.

As we can see, the permissions are adequate to create a session via TSQL and it does not cause SSMS to crash. This would be another consideration if using the XE Profiler (and even if you are going to allow non DBAs to create XE Traces) in addition to what I noted recently in this article – here.  Truth be told, the traditional GUI for XE also will not allow users to access the Extended Events node if they have “Alter Any Event” or lower permissions – you must use TSQL to alter sessions. The caveat being that only the XE Profiler feature causes SSMS to crash (I tried it 6 times in a row with consistent results).

So, if “Alter Any Event” is only going to get us partially there, let’s go ahead and grant the view server state permission.

Due to the nature of sys.traces, I still do not have permissions to view that data. So that is a good thing. On the other hand, I can now query all of my XE related views (you can read more about those in my 60 day series). And I now can see that I have the following permissions.

And now I can do all sorts of fun stuff from the XE GUI.

That basically lays out the required permissions for both Profiler and Extended Events. In either case, I view both of these as tools for the trusted DBA. Outside of the DBA team, there are select other individuals who may think they need the access and then there are others who really could use the access to the tools but should not really be granted full sysadmin access. At times, you may not even want those individuals to have anywhere near “view server state” access because there is a chance that individual could see something they are not permitted to see. What do we do with that?

Work Arounds

This is when we figure out a way to circumvent the restrictions. Don’t view this as a hack to the system or a way to violate trust. Rather these are methods to help think outside the box and work together as a cohesive team.

Method 1 is a solid solution. That said, it does have a high management overhead. Aaron Bertrand has offered a solution and it is a secure solution. Aaron has found a way to work with Trustworthy databases, views, and logins to help provide a workable solution while still securing the event session data. You can read his article here.

Method 2 is also a secure solution and does require a bit more communication but less management and upkeep than method 1. I like to save the event data to file. Working with the person that would like to be able to review the data, I can create an appropriate XE Session that will be highly unlikely to capture anything they should not see. In addition, I do not need to grant that person any elevated permission on the production server. Once the trace is complete, I can copy the event files to a dev server where that person has elevated access. Then they can merge the XEL files (I show how to do that in the 60 day series) and review the data at their leisure.

Method 3 is similar to the previous method but it requires more work similarly to method 1. This last method, I can grant the user specific access to read data from a specific table in a specific database. I then setup an automated process to dump the data into that table or I can manually load it for the user. Again, the permissions here are very limited in scope and the person has nothing more than the ability to Select from a pre-defined table in a pre-defined database.

I don’t see the permissions for Extended Events as a limitation, rather they seem appropriate based on what XE does and the extensive nature of the tool. I recommend you work with XE for a bit and see what you can come up with to help improve your efficiency and the efficiency of our team.



«page 1 of 9

March 2018
« Jan    

Welcome , today is Saturday, March 24, 2018