Edit

How to remove all the inactive sessions from the DEX_LOCK table in the TempDB database when you use Microsoft Dynamics GP together with Microsoft SQL Server

  • Article

This article describes how to remove all the inactive sessions from the DEX_LOCK table in the TempDB database by using Microsoft SQL Server.

Applies to:   Microsoft Dynamics GP
Original KB number:   864411

Summary

Delete the IDs for inactive sessions from the DEX_LOCK table by using the appropriate method, depending on the version of SQL Server that you use.

For SQL Server 2019 and later

  1. Show all the session IDs that are in the DEX_LOCK table that aren't associated with active sessions in the ACTIVITY table in the DYNAMICS database. To do it, run the following script in SQL Server Management Studio.

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

  2. Delete any ghost sessions in the DEX_LOCK table. To do it, run the following script in SQL Server Management Studio.

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

For more information about how to remove inactive sessions from the DEX_LOCK table, see How to remove all the inactive sessions from the DEX_SESSION table in the TempDB database when you use Microsoft Dynamics GP together with Microsoft SQL Server.