Removing stranded user sessions in Dynamics GP via SQL Server

In today's post I want to share a SQL script that I considered very important, lately I worked in cases were I have been witness on how other partners/customers deal in SQL to remove stranded users.

A few do this task from inside GP, others run the following code:

Delete from Activity where userid = 'XXXX"

But  lets  analyze,  they  are  not  really  removing  a  stranded  user  and  all  of  the  activity associated  with  the  user, what  they are really doing is just removing the record from the activity table so that Dynamics GP does not see the user, but what happens if they remove via  SQL  or GP the wrong user id? if the user is logged into 2 or more different companies at the same time?

Messages like "The selected document is being edited by another user" sounds familiar?

Let  me  explain  to  you how this SQL script works and then I'll provide it to you remember with this script you don't need the users to log off from Dynamics GP.

SQL server maintains a record of active user sessions in the table SYSPROCESSES from the  MASTER  database, inside sysprocesses there is a column named "login name" and we  base  our  script  on  sysprocesses  to  clear  the  ACTIVITY  table from the DYNAMICS database.

Once ACTIVITY table has  been  cleaned out we are ready to clean 2 tables from the TEMP database  first  we  clean  DEX_SESSION and then DEX_LOCK in order to eliminate locks and processes in temp tables.

The next step is to clean batch activity (SY00800) and resource activity (SY00801) in order to have a valid session clean up.

Here is the code:

delete from DYNAMICS..ACTIVITY

  where USERID not in (select loginame from master..sysprocesses)

delete from tempdb..DEX_SESSION

  where session_id not in (select SQLSESID from DYNAMICS..ACTIVITY)

delete from tempdb..DEX_LOCK

  where session_id not in (select SQLSESID from DYNAMICS..ACTIVITY)

delete from DYNAMICS..SY00800

  where USERID not in (select USERID from DYNAMICS..ACTIVITY)

delete from DYNAMICS..SY00801

  where USERID not in (select USERID from DYNAMICS..ACTIVITY)

Note: Make sure you have a backup of your databases when running scripts that can modify your data.

Also, I am including the KB's published that show how to remove records from DEX_LOCK and DEX_SESSION that working together make the script I just provided.

A batch is held in the Posting, Receiving, Busy, Marked, Locked, or Edited status in Microsoft Dynamics GP (KB 850289)

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 (KB 864411)

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 (KB 864413)