I want to share a small nugget I've created for listing who is currently running queries against AX at any point in time. It builds on two prerequisites, one of which is from one of my
suggested answers on the Dynamics AX Community Forum and secondly
a solution provided by Microsoft in their own blog.
For the sake of completeness I will include the "sp_lock3" stored procedure in this post, but it is only fair to include that I got it from the LearnSQLForum, posted by a Scott Wigham here:
http://forums.learnsqlserver.com/SqlServerTopic40.aspx
The sp_lock3 looks like this:
/*******************************************************
Source:
http://forums.learnsqlserver.com/SqlServerTopic40.aspx
******************************************************/
USE master
GO
IF ( SELECT OBJECTPROPERTY(OBJECT_ID('sp_lock3'), 'IsProcedure')) = 1
DROP PROC dbo.sp_lock3
GO
CREATE PROC dbo.sp_lock3 (
@spid1 INT = NULL /* Check only this spid; if this is NULL then all spids will be checked */
, @spid2 INT = NULL /* and this spid; if this is not null, @spid1 must be not null as well */
)
AS
CREATE TABLE #locktable (
spid SMALLINT
, loginname NVARCHAR(128)
, hostname NVARCHAR(128)
, dbid INT
, dbname NVARCHAR(128)
, objId INT
, ObjName NVARCHAR(128)
, IndId INT
, Type NVARCHAR(4)
, Resource NVARCHAR(16)
, Mode NVARCHAR(8)
, Status NVARCHAR(5)
)
SET NOCOUNT ON
IF @spid2 IS NOT NULL AND @spid1 IS NULL
SET @spid1 = @spid2
DECLARE @object_id INT,
@dbid INT,
@DynamicSql NVARCHAR(255)
/***** @spid1 is provided so show only the locks for @spid1 and @spid2 *****/
IF @spid1 IS NOT NULL
INSERT #locktable ( spid, loginname, hostname, dbid, dbname, objId, ObjName, IndId, Type, Resource, Mode, Status )
SELECT CONVERT (SMALLINT, l.req_spid)
, COALESCE(SUBSTRING (s.loginame, 1, 128), '')
, COALESCE(SUBSTRING (s.hostname, 1, 128), '')
, l.rsc_dbid
, SUBSTRING (DB_NAME(l.rsc_dbid), 1, 128)
, l.rsc_objid
, ''
, l.rsc_indid
, SUBSTRING (v.name, 1, 4)
, SUBSTRING (l.rsc_text, 1, 16)
, SUBSTRING (u.name, 1, 8)
, SUBSTRING (x.name, 1, 5)
FROM master.dbo.syslockinfo l JOIN master.dbo.spt_values v
ON l.rsc_type = v.number
JOIN master.dbo.spt_values x
ON l.req_status = x.number
JOIN master.dbo.spt_values u
ON l.req_mode + 1 = u.number
JOIN master.dbo.sysprocesses s
ON l.req_spid = s.spid
WHERE v.type = 'LR' AND x.type = 'LS' AND u.type = 'L' AND l.req_spid in (@spid1, @spid2) and l.rsc_dbid not in (32767)
ELSE /***** @spid1 is not provided so show all the locks *****/
INSERT #locktable ( spid, loginname, hostname, dbid, dbname, objId, ObjName, IndId, Type, Resource, Mode, Status )
SELECT CONVERT (SMALLINT, l.req_spid)
, COALESCE(SUBSTRING (s.loginame, 1, 128), '')
, COALESCE(SUBSTRING (s.hostname, 1, 128), '')
, l.rsc_dbid
, SUBSTRING (DB_NAME(l.rsc_dbid), 1, 128)
, l.rsc_objid
, ''
, l.rsc_indid
, SUBSTRING (v.name, 1, 4)
, SUBSTRING (l.rsc_text, 1, 16)
, SUBSTRING (u.name, 1, 8)
, SUBSTRING (x.name, 1, 5)
FROM master.dbo.syslockinfo l JOIN master.dbo.spt_values v
ON l.rsc_type = v.number
JOIN master.dbo.spt_values x
ON l.req_status = x.number
JOIN master.dbo.spt_values u
ON l.req_mode + 1 = u.number
JOIN master.dbo.sysprocesses s
ON l.req_spid = s.spid
WHERE v.type = 'LR' AND x.type = 'LS' AND u.type = 'L' and l.rsc_dbid not in (32767)
/**********************************************************************************************
Because the locks exist in any database, you must USE before running OBJECT_NAME
We use a dynamic SQL loop to loop through each row from #locktable
A temp table is required here since SQL Server 2000 cannot access a table variable when issuing dynamic sql
**********************************************************************************************/
-- Initialize the loop
SELECT TOP 1 @dbid = dbid, @object_id = ObjId FROM #locktable WHERE Type ='TAB' AND ObjName = ''
WHILE @dbid IS NOT NULL
BEGIN
SELECT @DynamicSql =
'USE ' + DB_NAME(@dbid) + char(13)
+ 'UPDATE #locktable SET ObjName = OBJECT_NAME('
+ CONVERT(VARCHAR, @object_id) + ') WHERE dbid = ' + CONVERT(VARCHAR, @dbId)
+ ' AND objid = ' + CONVERT(VARCHAR, @object_id)
EXEC sp_executesql @DynamicSql
SET @dbid = NULL -- TSQL preserves the "old" value unless you initialize it to NULL
SELECT @dbid = dbid, @object_id = ObjId FROM #locktable WHERE Type ='TAB' AND ObjName = ''
END
SELECT * FROM #locktable
WHERE objname NOT LIKE '#locktable_____%' -- don't return this temp table
AND objid > 100 -- do not return system table locks
AND objname <> 'spt_values'
GO
If you first install this procedure, simply by running it on the SQL Server instance, it will be globally available on that instance.
Next step is to run the script below to install my "sp_whoInAx":
/************************************************************************************
sp_whoInAx This script lists out current users running queries against
any Dynamics AX database on this SQL Server Engine instance.
Please report any issues and improvements back to the author.
Witten By: Tommy Skaue (email: add the @-sign between first and last name, and end with .com)
Microsoft Dynamics AX MVP
yetanotherdynamicsaxblog.blogspot.com
Version: 1.0
Comments: This procedure requires sp_lock3 in order to work.
This script is presented "AS IS" and has no warranties expressed or implied!!!
**********************************************************************************/
USE [master]
GO
IF ( SELECT OBJECTPROPERTY(OBJECT_ID('sp_whoInAx'), 'IsProcedure')) = 1
DROP PROC [dbo].[sp_whoInAx]
GO
CREATE PROCEDURE [dbo].[sp_whoInAx]
AS
CREATE TABLE #LOCKTABLE (
SPID SMALLINT
, LOGINNAME NVARCHAR(128)
, HOSTNAME NVARCHAR(128)
, DBID INT
, DBNAME NVARCHAR(128)
, OBJID INT
, OBJNAME NVARCHAR(128)
, INDID INT
, TYPE NVARCHAR(4)
, RESOURCE NVARCHAR(16)
, MODE NVARCHAR(8)
, STATUS NVARCHAR(5)
)
INSERT INTO #locktable
EXEC sp_lock3
CREATE TABLE #CurrentAxSessions (
CI VARCHAR(128)
, HOST_NAME VARCHAR(128)
, SESSION_ID smallINT
, DATABASE_ID smallINT
, LOGIN_TIME datetime
, STATUS VARCHAR(30)
)
INSERT INTO #CurrentAxSessions
SELECT
CAST(CONTEXT_INFO AS VARCHAR(128)) AS CI
, HOST_NAME
, SESSION_ID
, DATABASE_ID
, LOGIN_TIME
, STATUS
--, STATUS, CPU_TIME,MEMORY_USAGE, TOTAL_SCHEDULED_TIME, TOTAL_ELAPSED_TIME
--, LAST_REQUEST_START_TIME, LAST_REQUEST_END_TIME, READS, WRITES, LOGICAL_READS, OPEN_TRANSACTION_COUNT
FROM SYS.DM_EXEC_SESSIONS
WHERE 1=1
AND PROGRAM_NAME LIKE '%DYNAMICS%'
AND CAST(CONTEXT_INFO AS VARCHAR(128)) <> ''
SELECT
AX.CI
,AX.LOGIN_TIME
,AX.SESSION_ID
,AX.STATUS
,LT.HOSTNAME
,LT.DBNAME
,LT.OBJNAME
FROM #CurrentAxSessions AX
INNER JOIN #locktable LT ON LT.DBID = AX.DATABASE_ID AND LT.SPID = AX.SESSION_ID
GO
It takes the result of sp_lock and combines it with the "AX sessions". Again, it does require that
registry change mentioned in Microsofts blog, in addition to a restart of the AOS service.
To reiterate the steps for including the user in the session context, they are:
- Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Dynamics Server\6.0\01\Original (installed configuration). The last key, Original (installed configuration), is the key name for the current server configuration. If your system uses a different configuration that the original installed configuration, navigate to the currently active configuration.
- Create a string registry value called ‘connectioncontext’ and set the value to 1.
- Restart the AOS.
Now you can run sp_whoInAx from any query window on that SQL Server instance, and see from the context the user id and also see what database and tables are involved.
