In this month’s blog, I wanted to address the primary reasons records may be locked in Dynamics GP and various ways to unlock the records. The causes of records being locked in Dynamics GP may happen for a variety of reasons, but the fix is quite similar. Two of the primary reasons records may be locked in Dynamics GP are from batches having issues while being posted and records being edited by a user. The records remain locked until the batch is posted or the user has saved their changes. If something happens during these processes, the lock may not be released and the record could remain locked.
Below are some SQL Queries that will assist in troubleshooting locked records:
To review current users accessing Dynamics GP:
SELECT * FROM DYNAMICS..ACTIVITY
-
To review current locks in the Batch Activity Table detailing User, Company, Batch, and Trx Type. (Linked to the ACTIVITY table on the USERID field):
select * FROM DYNAMICS..SY00800
-
To review current resources being utilized by active users detailing User, Company, and Process. (Linked to the ACTIVITY table on the USERID field):
select * FROM DYNAMICS..SY00801
-
To review temporary locks in Dynamics GP. (Linked to the ACTIVITY table SQLSESID field):
select * FROM tempdb..DEX_LOCK
-
To review Dynamics GP active sessions. (Linked to the ACTIVITY table SQLSESID field):
select * FROM tempdb..DEX_SESSION
-
To Review Dynamics GP Batch detailing Batch details such as Batch Name, User, Status, etc.
Select * from [Company DB]..SY00500
Let us take a look at the reasons for the locks in Dynamics GP. During the batch posting process, the appropriate records being posted are automatically locked by the posting user until the posting is complete. The records are automatically unlocked once the posting is complete. This ensures the records being posted are not manipulated during the posting process. Another cause is from users editing a record with changes prior to the record being saved.
If an issue occurs during the posting process or editing of a Dynamics GP record, and a power fluctuation or other problem happens during the posting causing the posting process to stop, the batch or record may be locked in the Posting, Receiving, Busy, Marked, Locked, or Edited status. The user may also get the message “Batch is marked for posting by another user.” When trying to open the batch.
-
To fix this issue, have everyone logout of Dynamics GP and run the following SQL scipts (Please note this will remove any active users, so make sure everyone is out of Dynamics GP):
DELETE DYNAMICS..ACTIVITY
DELETE DYNAMICS..SY00800
DELETE DYNAMICS..SY00801
DELETE TEMPDB..DEX_LOCK
DELETE TEMPDB..DEX_SESSION
-
Or if the User is known, have the particular user logout and run the following SQL scripts. (Perform the ACTIVITY table deletion after the other scripts are completed):
DELETE DYNAMICS..SY00800 WHERE USERID in (select USERID from DYNAMICS..ACTIVITY WHERE USERID=’UUUU’)
DELETE DYNAMICS..SY00801 WHERE USERID in (select USERID from DYNAMICS..ACTIVITY WHERE USERID=’UUUU’)
DELETE tempdb..DEX_LOCK WHERE session_id IN ( select SQLSESID from DYNAMICS..ACTIVITY WHERE USERID=’UUUU’)
DELETE tempdb..DEX_SESSION WHERE session_id IN ( select SQLSESID from DYNAMICS..ACTIVITY WHERE USERID=’UUUU’)
DELETE DYNAMICS..ACTIVITY WHERE USERID =’UUUU’
-
(‘UUUU’ is the USERID that is locked)
-
Please note this will clear all user records in the Dynamics database, but a session may still be locked in the tempdb tables. If this is the case and the records can not be identified, then you will have to get all users out of Dynamics GP and run the SQL statements in the fix above for all users.
-
Or if the Company is known, have the users for the Company logout and run the following SQL scripts. (Perform the ACTIVITY table deletion after the other scripts are completed):
DELETE DYNAMICS..SY00800 WHERE USERID in (select USERID from DYNAMICS..ACTIVITY WHERE USERID= ‘CCCC’)
DELETE DYNAMICS..SY00801 WHERE USERID in (select USERID from DYNAMICS..ACTIVITY WHERE USERID= ‘CCCC’)
DELETE tempdb..DEX_LOCK WHERE session_id IN ( select SQLSESID from DYNAMICS..ACTIVITY WHERE USERID= ‘CCCC’)
DELETE tempdb..DEX_SESSION WHERE session_id IN ( select SQLSESID from DYNAMICS..ACTIVITY WHERE USERID= ‘CCCC’)
DELETE DYNAMICS..ACTIVITY WHERE USERID = ‘CCCC’
-
(‘CCCC’ is the CMPNYNAM that is locked)
-
Please note this will clear all user records in the Dynamics database, but a session may still be locked in the tempdb tables. If this is the case and the records can not be identified, then you will have to get all users out of Dynamics GP and run the SQL statements in the fix above for all users.
-
After the locks are removed, run the following script to update the Dynamics GP Batch status:
UPDATE [Company DB]..SY00500 SET MKDTOPST=0, BCHSTTUS=0 where BACHNUMB=‘XXXX’
-
(‘XXXX’ is the batch number that is locked)
-
[Company DB] is the Dynamics Database containing the company data.
ANDREW FRASER | Senior Business Software Consultant
As a Senior Business Consultant, Andrew is responsible for the implementation and support of CRM, GP, and other Microsoft products assuring that our solution scoping, architecting, and delivery meet and exceed customer expectations. He helps clients analyze their current business processes and provide them with process improvements. During his time at KTL, Andrew has built lasting relationships with clients by understanding their business needs and provided value by leveraging their existing resources. Andrew has more than nineteen years of experience in implementing business software for a variety of industries including service, government, distribution, not-for-profit and manufacturing. He graduated with a Bachelor’s Degree in Math with concentration in Computer Science and a Bachelor’s Degree in Business Marketing from St. Andrew’s University in Laurinburg, North Carolina. His experience includes helping companies embrace technology to fulfill their organizational needs and creating innovative solutions using CRM, GP and other Microsoft products.
