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:DatabaseXEServerandAuditChange.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.
SELECTxo.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
FROMsys.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
WHERExo.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 FROMsys.dm_xe_objects xo WHERExo.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.
SELECTxo.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
FROMsys.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
WHERExo.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.
