RSMUS.comLocked records and stuck batches in Dynamics GP can happen for a variety of reasons including temporary loss of network connectivity, transactions left open by users or data issues. Clearing these records can be a huge inconvenience for users because the supported solution from Microsoft would ask to have all users logged out and run a complete removal of all user activity. See Microsoft’s recommendation for releasing stuck batches and locked records by clicking here.
Microsoft recommends that all users exit the system and to run the following scripts:
DELETE DYNAMICS..ACTIVITY
DELETE DYNAMICS..SY00800
DELETE DYNAMICS..SY00801
DELETE TEMPDB..DEX_LOCK
DELETE TEMPDB..DEX_SESSION
Having all users exit the system during work hours is impractical, inconvenient and normally not feasible. Fortunately, there is another option that involves linking these tables to the master..sysprocesses to locate the stuck records.
Run the scripts below to find the problem records:
SELECT * FROM DYNAMICS..ACTIVITY WHERE USERID not in
(select loginame from master..sysprocesses)
SELECT * FROM tempdb..DEX_SESSION WHERE session_id not in
(select SQLSESID from DYNAMICS..ACTIVITY)
SELECT * FROM tempdb..DEX_LOCK WHERE session_id not in
(select SQLSESID from DYNAMICS..ACTIVITY)
SELECT * FROM DYNAMICS..SY00801 WHERE USERID not in
(select USERID from DYNAMICS..ACTIVITY)
SELECT * FROM DYNAMICS..SY00801 WHERE USERID not in
(select USERID from DYNAMICS..ACTIVITY)
–You must have permissions to see the master..sysprocesses table to run this script or the first statement will produce false positives.
After you’ve identified these records, use the following scripts to delete them:
DELETE DYNAMICS..ACTIVITY WHERE USERID not in
(select loginame from master..sysprocesses)
DELETE tempdb..DEX_SESSION WHERE session_id not in
(select SQLSESID from DYNAMICS..ACTIVITY)
DELETE tempdb..DEX_LOCK WHERE session_id not in
(select SQLSESID from DYNAMICS..ACTIVITY)
DELETE DYNAMICS..SY00801 WHERE USERID not in
(select USERID from DYNAMICS..ACTIVITY)
DELETE DYNAMICS..SY00801 WHERE USERID not in
(select USERID from DYNAMICS..ACTIVITY)
To learn more about how you can take advantage of this and other Dynamics GP features, visit RSM’s Microsoft Dynamics GP resource. To make sure you stay up to date with the Microsoft Dynamics Community, subscribe to our Microsoft Dynamics Community Newsletter. For more information on Microsoft Dynamics 365, contact us.
By: Ashwyn Sangha
