Why is context_info sometimes blank in sys.dm_exec_sessions?

by Sanket   Last Updated March 29, 2016 08:02 AM

I am trying to get information from the context_info column using Cast(context_info as varchar(128)) from sys.dm_exec_sessions as given in the article below:

Finding User Sessions from SPID in Dynamics AX 2012 by Amit R. Kulkarni

However, for some rows, the context_info column is empty. Does anyone know why this is?

The database is a Dynamics AX database and the query I'm using is:

select cast(context_info as varchar(128)) as ci,* 
from sys.dm_exec_sessions 
where program_name like '%Dynamics%'

Here is the Output, I have had to remove a couple of columns but this is pretty much how it looks. If you see, The Context_info is blank for session 63 and 87.

enter image description here

Tags : sql-server dmv

Answers 2

From CONTEXT_INFO (Transact-SQL) in the documentation:

If context_info was not set:

  • In SQL Server returns NULL.
  • In [Azure] SQL Database returns a unique session-specific GUID.
March 22, 2016 09:29 AM

CONTEXT_INFO needs to be set by the application and Dynamics AX sets the value for the connections included in the session. If CONTEXT_INFO is blank it really is because the application didn't set the property.

When AX uses some connections for a specific session one can see the setting of the CONTEXT_INFO in ETW traces like this:

enter image description here

I did several tests and I couldn't create a situation where the CONTEXT_INFO is not set correctly, even when constructing my own ODBCconnection or using the Connection() class from within AX.

Since you have a situation where for some sessions CONTEXT_INFO it really looks like you have an Object Server somewhere that isn't configured to set the context information for it's sessions.

If you look closely at your results, you will see that both empty rows have the same host process id, and you don't have any rows where that process id does return a context_info.

enter image description here

According to the documentation on sys.dm_exec_sessions the host_process_id is the process id of the client application.

Process ID of the client program that initiated the session. The value is NULL for internal sessions. Is nullable.

What I think is happening is that you have either multiple AX environments on this server, or you have multiple AOS instances where one of them isn't configured according to the article you linked.

For example I have a set-up with a couple of instances on one machine, connecting to a SQL Server and when querying the SQL Server sys.dm_exec_sessions the CONTEXT_INFO is blank for the instances where I didn't configure it, and never blank for the instance I did configure correctly.

These processes:
enter image description here

Show up in the sessions on SQL Server:

enter image description here

However, since I only configured one of the instances if I look at the context info query I get this result (with a bunch of blanks)

enter image description here

My conclusion is that you likely have an instance somewhere where this registry configuration is not properly activated.

Tom V
Tom V
March 29, 2016 11:46 AM

Related Questions

Linked Server Error on SQL Agent Job

Updated March 16, 2016 08:02 AM