RES 3700 - Error: XXXX number of checks have not been purged.

This is an error which indicates that old closed checks have not been purged from the database.
Two tables contain check details which need to be purged:
micros.chk_dtl and micros.trans_dtl

Micros 3700 is hard coded to keep check detail for 14 days. If the database fails to purge this data after 14 days it starts to give the "XXXX checks have not been purged" where XXXX is the number of check detail.

There is a stored procedure that should be in the End of Day/Night Audit steps called Purge History. This calls a stored procedure:
micros.sp_purgehistory();

Steps to resolve:

Step 1) Make sure the End of Day is being run at the end of your business date. If it is not being run then it wont be able to run the necessary stored procedure. As part of your end of day there should be a step configured which is a stored procedure called Purge Historical Totals.

Step 2) If the End of Day is being run then something is not working as it should.
Things to check:
a) Make sure you don't have loads of open checks from weeks ago on the system. If you do then close them off. After the end of day runs again it should purge the necessary data.
b) Make sure the purge step is part of the end of day.

Step 3) If still having problems open DBISQL. If you dont know what DBISQL is then click here for a quick tutorial on what it is. 

Run the following query:

Select min(business_date) from micros.trans_dtl

This will return to you a date. If the purge is running correctly this date should be no more than 14 days before the current business date. In your case it may be out by a few days or weeks depending on how long you have been seeing the error message. Make a note of this date.

Run the following in dbisql:
call micros.sp_purgehistory();

Depending on how many days out your purge is this can take from a few seconds up to 10-15 minutes. (Ive seen it take an hour on an old system where the purge didn't run for months.)

After the procedure completes in the result section of dbisql it will say Procedure Completed.

Now run this statement again:
Select min(business_date) from micros.trans_dtl

The date should have changed to a more recent date. Keep running the "call micros.sp_purgehistory();" procedure until the date is to 14 days from the current business date.

You should now find that the errors on your tills have gone.

If your still having problems then contact your micros support as there may be some issues with the micros.chk_dtl or micros.trans_dtl database tables.

Im not going to get into diagnosing these problems here yet. If you believe you have a strong grasp of SQL and are comfortable updating and deleting rows from the SQL database then drop me an email and ill tell you what to look for.


2 comments:

  1. Is there a way to specify just purging 1 day (at a time) because when I try to run "call micros.sp_purgehistory();" it runs forever and freezes our system out front. I ran it at night once, and let it go for over an hour, and it still seemed stuck. I wonder if there are now too many days for it to handle the purge. any ideas?

    ReplyDelete
  2. ---- STARTING - Purge
    ==============================================
    Sequence # 19002 Step # 1
    We get an error 689 checks have not been purged.

    Purge Historical Totals
    Sequence # 19002 Step # 1 Stored Procedure Step Error.
    Error executing stored procedure.
    ==============================================
    ---- DID NOT COMPLETE ----

    ReplyDelete

-->

Disclaimer

All information on this web site is correct to the best of my knowledge at the time of writing.
I will not be held responsible for any damage caused through information on this website.