Message in a Bottle of XE

, 2017-12-27

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.

Messages

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).

CREATE EVENT SESSION ThrowError ON SERVER
ADD EVENT sqlserver.errorlog_written
ADD TARGET package0.event_file ( SET filename = N'Z:\Database\XE\ServerandAuditChange.xel' );
GO

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.

SELECT m.message_id, m.text
	FROM sys.messages m
		INNER JOIN sys.syslanguages l
			ON m.language_id = l.lcid
	WHERE l.name = 'us_english'
		AND m.message_id = 25641;

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.

SELECT DISTINCT object_type FROM sys.dm_xe_objects xo;

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.

SELECT	xo.name AS MsgName
	, xo.description AS MsgText
	, xp.name AS PackageName
	--, xp.description AS PkgDescription
	, REVERSE(LEFT(REVERSE(olm.name), CHARINDEX('\', REVERSE(olm.name)) - 1)) AS DLLName
	, olm.file_version
	, olm.product_version
FROM	sys.dm_xe_objects xo
		INNER JOIN sys.dm_xe_packages xp
			ON xo.package_guid = xp.guid
		INNER JOIN sys.dm_os_loaded_modules olm
			ON xp.module_address = olm.base_address
		LEFT OUTER JOIN sys.dm_xe_object_columns oc
			ON xo.name = oc.object_name
WHERE	xo.object_type = 'message'
ORDER BY xo.name ASC;

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

/*correlate xe messages to sql messages - nope */
SELECT m.message_id, m.text
	FROM sys.messages m
		INNER JOIN sys.syslanguages l
			ON m.language_id = l.lcid
	WHERE l.name = 'us_english'
		AND m.text IN (SELECT xo.description AS MsgText
			FROM	sys.dm_xe_objects xo
			WHERE	xo.object_type = 'message'
			);

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.

SELECT	xo.name AS MsgName
	, xo.description AS MsgText
	, xp.name AS PackageName
	--, xp.description AS PkgDescription
	, REVERSE(LEFT(REVERSE(olm.name), CHARINDEX('\', REVERSE(olm.name)) - 1)) AS DLLName
	, olm.file_version
	, olm.product_version
FROM	sys.dm_xe_objects xo
		INNER JOIN sys.dm_xe_packages xp
			ON xo.package_guid = xp.guid
		INNER JOIN sys.dm_os_loaded_modules olm
			ON xp.module_address = olm.base_address
		LEFT OUTER JOIN sys.dm_xe_object_columns oc
			ON xo.name = oc.object_name
WHERE	xo.object_type = 'message'
	AND xo.description LIKE '%parameter%is invalid%'
ORDER BY xo.name ASC;
SELECT m.message_id, m.text
	FROM sys.messages m
		INNER JOIN sys.syslanguages l
			ON m.language_id = l.lcid
	WHERE l.name = 'us_english'
		AND m.text LIKE '%parameter%is invalid%';

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads