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

Applies to: Dynamics GP 2010

Summary


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

More Information


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 2000

  1. Show 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. To do this, run the following script in SQL Query Analyzer.
    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 this, run the following script in Query Analyzer.
    DELETE TempDB..DEX_LOCK where Session_ID not in (SELECT SQLSESID from DYNAMICS..ACTIVITY)

For SQL Server 2005 and SQL Server 2008

  1. Show 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. To do this, 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. 2.Delete any ghost sessions in the DEX_LOCK table. To do this, 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, click the following article number to view the article in the Microsoft Knowledge Base:

864413 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