Releasing Stuck Batches and Transactions without exiting all Users


David MeegoFrom the Useful SQL Scripts Series.


When a system has a batch that is stuck as marked to post or a transaction that cannot be accessed as it says it is already being edited, the usual instructions are to exit all users from the system and then delete the contents of the activity tracking, and Dexterity session and lock tables.


These are the steps listed in Knowledge Base (KB) article 850289 (see link below) and while they work fine, they require an interruption to work for all users in all companies.  On a small system in single location that can usually be organised. However, on a large system or worse a multi-national 24/7 system that can be extremely difficult to organise.


Wouldn’t it be nicer if we could just remove the activity records and reset batches only for the users currently not in the system while the rest of the users can continue their work.  Well now you can.



My friend, Robert Cavill pointed out KB articles 864411 & 864413 (see links below) which can cleanup the DEX_SESSION and DEX_LOCK tables for users no longer in the system based on the ACTIVITY table while the system is still in use. 


Note: The scripts suggested in KB articles 864411 & 864413, will need to be changed to use tempdb and session_id to work on a case sensitive (binary) system.


Taking this concept and bringing it to the next level, I have created a script that will remove records from the following system tables if it cannot find the user in the DYNAMICS..ACTIVITY table.




  • DEX_SESSION


  • DEX_LOCK


  • SY00800 (SY_Batch_Activity_MSTR)


  • SY00801 (SY_ResourceActivity)

For batches which have records in the SY00800 (SY_Batch_Activity_MSTR) which will be removed and the Batch Status is between 1 and 6, the script will reset the Batch Status, Marked to Post and User ID fields in the company SY00500 (Batch_Headers) table across all companies.


KB Article 852420 (see link below) provides more information on Batch Status values as well as Victoria Yudin’s post on Company/System Tables.


The script below can be used while users are logged into the application and will clean up activity records for the system and all company tables in one pass.  It leverages the sp_MSforeachdb stored procedure mentioned in the Running SQL commands against all GP Company Databases post.


If you need a particular user/company to be cleaned up, just make sure there is no record for that User ID and Company ID in the DYNAMICS..ACTIVITY table before running the script. 


SQL Script to remove all Activity Records for users currently not logged in




— Dexterity Sessions Table
delete S
— select *
from tempdb..DEX_SESSION S
where not exists (
 select * from DYNAMICS..ACTIVITY A
 where S.session_id = A.SQLSESID)


— Dexterity Locks Table
delete L
— select *
from tempdb..DEX_LOCK L
where not exists (
 select * from DYNAMICS..ACTIVITY A
 where L.session_id = A.SQLSESID)


— Batch_Headers table in each company
exec sp_MSforeachdb
‘ use [?]
if exists ( select INTERID from DYNAMICS..SY01500 D where INTERID = ”?” )
begin
 update S set BCHSTTUS = 0, MKDTOPST = 0, USERID = ””
 — select *
 from SY00500  S
 where BCHSTTUS in (1,2,3,4,5,6)
 and not exists (
  select * from DYNAMICS..ACTIVITY A
  JOIN DYNAMICS..SY01500 C ON C.CMPNYNAM = A.CMPNYNAM
  where S.USERID = A.USERID and C.INTERID = db_name())
 and exists (
  select * from DYNAMICS..SY00800 B
  where not exists (
   select * from DYNAMICS..ACTIVITY A
   where B.USERID = A.USERID and B.CMPNYNAM = A.CMPNYNAM)
  and S.BCHSOURC = B.BCHSOURC and S.BACHNUMB = B.BACHNUMB)
 print ””
 print ”(” + ltrim(str(@@ROWCOUNT)) + ” row(s) affected) – Database ” + db_name()
end


— SY_Batch_Activity_MSTR table
delete  B
— select *
from DYNAMICS..SY00800 B
where not exists (
 select * from DYNAMICS..ACTIVITY A
 where B.USERID = A.USERID and B.CMPNYNAM = A.CMPNYNAM)


— SY_ResourceActivity table
delete  R
— select *
from DYNAMICS..SY00801 R
where not exists (
 select * from DYNAMICS..ACTIVITY A
 JOIN DYNAMICS..SY01500 C ON C.CMPNYNAM = A.CMPNYNAM
 where R.USERID = A.USERID and R.CMPANYID = C.CMPANYID)


If you want to see the records that will be deleted or changed, you can use — to comment out the delete and update lines and remove the — from the select * lines.


 


For more information please see the KB articles referenced in this post.


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


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) Secure Link


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) Secure Link


Batch status codes (BCHSTTUS) in the SY00500 Posting Definitions master table (KB 852420) Secure Link


 


The script is also available as an attachment at the bottom of this post.


While this is not strictly development related, this is something that should make the job of a system administrator much easier.  Once you are comfortable with this script, you could schedule it to occur on a regular basis using a SQL Agent job.


You might also want to look at the Automated Solutions, the links are at the bottom of the General Articles & Links page.


Let me know if you find this useful.


David


04-Dec-2008: Updated script to have [?] on the use statement as suggested by Andrew Cooper’s comment.

SQL Cleanup Activity.zip

Comments (12)

  1. Andrew Cooper says:

    This is an extremely useful script.

    I would propose one amendment however – that the "?" in "use ?" is enclosed in square-braces, as per "use [?]".

    This ensures that databases with hyphens in the name are handled correctly.

    Other than that, another high-quality and well written post!

  2. This is a great article and a very useful script for all the technical consultants who directly support their customers.

    We come across these type of issues very often with our customers.

    Thanks to David.

  3. Based on the success of the "Resolving Security Issues in Dynamics GP" located here, Microsoft Dynamics

  4. Based on the success of the "Resolving Security Issues in Dynamics GP" located here, Microsoft

  5. David Musgrave says:

    Posting from Ron Wilson, the Real Life Dynamics User

    rldu.wordpress.com/…/sql-scripts-clear-users-and-activity-locks

  6. Hussain Al-Yousef says:

    I'm using this SQL Script since almost two years until now, it's a very good work. So much helpful.

    Thank you David.

  7. Has something changed with the script recently. It used to work perfectly. Now I get incorrect syntax errors (Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ‘—’.
    Msg 102, Level 15, State 1, Line 12
    Incorrect syntax near ‘—’.
    Msg 102, Level 15, State 1, Line 21
    Incorrect syntax near ‘—’.
    Msg 911, Level 16, State 1, Line 23
    Database ‘?’ does not exist. Make sure that the name is entered correctly.)

    1. Hi Manuel

      Is your System Database still called DYNAMICS? this script uses DYNAMICS, if you have changed your system database name (as is allowed in the newer versions), you will need to edit the script accordingly.

      David

  8. Irene says:

    Thank you!