Tuesday, March 20, 2012

Removing stranded user sessions in Dynamics GP via SQL Server


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. 

No comments:

Post a Comment