Fixing stuck batches in Dynamics GP

Locked 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: