Dynamics Community Forum Thread Details

Dex_Lock and Dex_Session tables

Hello GP Folks,

Recently we found out that we have some old SQL custom code written which is depending on DEX_LOCK table. All i know is, these 2 tables(DEX_Lock and DEX_Session) resides on tempdb and we have to clean these tables as part of clearing GP activity. I am able to find only this information in online. I would like to know in and out of these tables so that i can understand the custom code. Can someone please provide online link or document reference which provides detail explanation about these tables. I am mainly looking for below questions, I tried to search all GP documentation including sdk but i didn't find information anywhere. Thanks in advance for help.

--> When and How the data gets inserted in these tables

--> What kind of data these tables usually stores

The DEX_LOCK and DEX_SESSION tables get created by Dynamics GP when you install the GP databases onto a SQL instance and/or when you migrate existing GP databases to a new SQL instance and run the DEX_REQ.sql script found in KB 878449, which also creates a smDEX_Build_Locks stored procedure in the Master database.

The main table used is the DEX_SESSION table which keeps track of user login activity and without this table, not even 'sa' will be able to login to the Dynamics GP application.

My understanding, looking at information on these two tables, is that they are used by Dexterity with the DEX_ROW_ID columns in each GP table to allow two users to update the same record of the same table, as long as it is different columns being updated.

When a user logs out of Dynamics GP, the record in the DEX_SESSION table, similar to the ACTIVITY system table, should be removed. This is also true if the SQL service or server is rebooted.

This script shows all the session IDs that are in the DEX_LOCK table that are not associated with active sessions in the ACTIVITY table in the DYNAMICS database:

SELECT * from TempDB..DEX_LOCK where Session_ID not in (SELECT SQLSESID from DYNAMICS..ACTIVITY)

You can delete any ghost sessions in the DEX_LOCK table by running this script:

DELETE TempDB..DEX_LOCK where Session_ID not in (SELECT SQLSESID from DYNAMICS..ACTIVITY)

To find any inactive user sessions, run this script:

SELECT * from TempDB..DEX_SESSION where Session_ID not in (SELECT SQLSESID from DYNAMICS..ACTIVITY)

You can delete the inactive user sessions in the DEX_SESSION table by running this script:

DELETE TempDB..DEX_SESSION where Session_ID not in (SELECT SQLSESID from DYNAMICS..ACTIVITY

Thanks

Quick Links

Leaderboard