Clearing locked Dynamics GP batches / transactions

More than once we've run into an issue where a batch is stuck, a reconcile cannot be run, a transaction cannot be edited, among other things. Normally you would have to get everyone out of the system and wipe out a few SQL tables to clear the blockage. Sometimes you have a lot of users to notify to cease and desist all work in GP, log off, wait a few minutes, notify everyone they can log back in, and, finally, they log back in to continue their jobs. For a medium to large business this can be a serious resource downtime (ie: 15 minutes x 20 users = 5 hours lost). Plus, users may have stepped away from their desk or are simply not available to log off.

The error messages you will get that could use a script like this are "Sorry, another user is editing this document" (KB865003), "You cannot complete this process while transactions are being edited" (KB864434), "Item is in use" (KB855665), "Another user is doing this operation" (KB873851), "You cannot complete this process while invoices are being posted" (KB865739), "Cannot complete reconcile while someone is posting" (KB855277), "Another user is printing or calculating 1099s" (KB859915).

If you get "This asset is being retired by another user. Please try later." (KB860373) or "Batch is marked for posting by another user" (KB850289), you can run this script, but there are more scripts you will need to run afterwards. See the KB article on PartnerSource/CustomerSource for more information.

As usual, use these at your own risk. And don't forget to make backups first!

/*
    This script will clear out entries in the following tables,
        in order that you don't have to log everyone out to clear
        out orphaned records and transaction/batch locks:
            DYNAMICS..Activity
            tempdb..DEX_SESSION
            tempdb..DEX_LOCK
            DYNAMICS..SY00800
            DYNAMICS..SY00801
        The Activity will be cleared out first for orphaned records
        Then the DEX_SESSION will be cleared out for orphaned records
        Then the DEX_LOCK will be cleared out for orphaned records
*/
--    Remove orphaned entries in Activity table
--        by matching Activity, DEX_SESSION, and SysProcesses tables
--        to find Activity not associated with current SQL sessions/processes
--    Make sure to run this DELETE script before the others

    DELETE FROM DYNAMICS..Activity
    WHERE SQLSESID NOT IN (
        SELECT SQLSESID
        FROM DYNAMICS..Activity A
        INNER JOIN tempdb..DEX_SESSION S
        ON A.SQLSESID = S.session_id
        INNER JOIN master..sysprocesses P
        on S.sqlsvr_spid = P.spid
        AND A.USERID = P.loginame
    )

--    Remove orphaned entries in DEX_LOCK and DEX_SESSION
--        by deleting any records not found in Activity

    DELETE FROM tempdb..DEX_LOCK
    WHERE session_id NOT IN (
        SELECT SQLSESID
        FROM DYNAMICS..ACTIVITY
    )

    DELETE FROM tempdb..DEX_SESSION
    WHERE session_id NOT IN (
        SELECT SQLSESID
        FROM DYNAMICS..ACTIVITY
    )

--    Remove orphaned entries in DYNAMICS..SY00800 (Batch Activity)

    DELETE FROM DYNAMICS..SY00800
    WHERE DEX_ROW_ID NOT IN (
        SELECT B.DEX_ROW_ID
        FROM DYNAMICS..SY00800 B
        INNER JOIN DYNAMICS..ACTIVITY A
            ON B.USERID = A.USERID
            AND B.CMPNYNAM = A.CMPNYNAM
    )

--    Remove orphaned entries in DYNAMICS..SY00801 (Resource Activity)

    DELETE FROM DYNAMICS..SY00801
    WHERE DEX_ROW_ID NOT IN (
        SELECT R.DEX_ROW_ID
        FROM DYNAMICS..SY00801 R
        INNER JOIN (
            DYNAMICS..ACTIVITY A
            INNER JOIN DYNAMICS..SY01500 C
                ON A.CMPNYNAM = C.CMPNYNAM)
            ON R.USERID = A.USERID
            AND R.CMPANYID = C.CMPANYID
    )

--    Look for DEX_LOCKs not associated with currently logged in users

    SELECT
        DL.table_path_name,
        A.USERID,
        A.CMPNYNAM
    FROM tempdb..DEX_LOCK DL
        LEFT JOIN DYNAMICS..ACTIVITY A
            ON DL.session_id = A.SQLSESID

--    Values in the USERID and CMPNYNAM columns show which user is
--        tied to that table; have the user log out
--        then run the above SQL statement again
--        to make sure the DEX_LOCK is cleared

/*
--    *******************************************************
--    Select code from the lines below if you want to view what
--        you will be removing in the code above
--    *******************************************************

--    Match Activity, DEX_SESSION, and SysProcesses tables
--        to find Activity not associated with SQL sessions/processes

    SELECT *
    FROM DYNAMICS..Activity
    WHERE SQLSESID NOT IN (
        SELECT SQLSESID
        FROM DYNAMICS..Activity A
        INNER JOIN tempdb..DEX_SESSION S
            ON A.SQLSESID = S.session_id
        INNER JOIN master..sysprocesses P
            ON S.sqlsvr_spid = P.spid
            AND A.USERID = P.loginame
    )

--    View orphaned entries in DEX_LOCK and DEX_SESSION

    SELECT *
    FROM tempdb..DEX_LOCK
    WHERE session_id NOT IN (
        SELECT SQLSESID
        FROM DYNAMICS..ACTIVITY
    )

    SELECT *
    FROM tempdb..DEX_SESSION
    WHERE session_id NOT IN (
        SELECT SQLSESID
        FROM DYNAMICS..ACTIVITY
    )

--    View orphaned entries in DYNAMICS..SY00800 (Batch Activity)

    SELECT * FROM DYNAMICS..SY00800
    WHERE DEX_ROW_ID NOT IN (
        SELECT B.DEX_ROW_ID
        FROM DYNAMICS..SY00800 B
        INNER JOIN DYNAMICS..ACTIVITY A
            ON B.USERID = A.USERID
            AND B.CMPNYNAM = A.CMPNYNAM
    )

--    View orphaned entries in DYNAMICS..SY00801 (Resource Activity)

    SELECT * FROM DYNAMICS..SY00801
    WHERE DEX_ROW_ID NOT IN (
        SELECT R.DEX_ROW_ID
        FROM DYNAMICS..SY00801 R
        INNER JOIN (
            DYNAMICS..ACTIVITY A
            INNER JOIN DYNAMICS..SY01500 C
                ON A.CMPNYNAM = C.CMPNYNAM)
            ON R.USERID = A.USERID
            AND R.CMPANYID = C.CMPANYID
    )

-- ***************************************************
-- ***************************************************

--    If all else fails, get everyone out and run the usual process:

--    MAKE SURE EVERYONE IS OUT OF THE SYSTEM before you run this:

    DELETE FROM DYNAMICS..SY00800
    DELETE FROM DYNAMICS..SY00801
    DELETE FROM DYNAMICS..ACTIVITY
    DELETE FROM tempdb..DEX_LOCK
    DELETE FROM tempdb..DEX_SESSION

---    View the contents of the above tables

    SELECT * FROM DYNAMICS..Activity
    SELECT * FROM tempdb..DEX_SESSION
    SELECT * FROM tempdb..DEX_LOCK
    SELECT * FROM DYNAMICS..SY00800
    SELECT * FROM DYNAMICS..SY00801

--    The currently connected SQL sessions
    SELECT * FROM master..sysprocesses


*/