Micros POS Help Blog - Micros POS Tools

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

6/28/2012

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, micros.trans_dtl and micros.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.

22 Comments

Gauge Gonzalez

7/5/2012 08:56:23 am

When i run the min(business_date) from micros.trans_dtl statement, I show a date back to 2009. When I run the purge statement, I stays running. I've left it over night and havent had any luck. What do I need to look for to update and delete rows. I really dont want to clear all my totals..

MicrosPosTools

7/6/2012 04:49:41 am

When you say "havent had any luck" do you mean the min business date is not increasing? If its increasing then you just need to keep running it. The more you run it the quicker it will get.

If its not increasing then run:
select business_date from micros.trans_dtl order by 1 asc

Are there many lines returned for very old dates?

Gauge Gonzalez

7/6/2012 06:01:54 am

I mean that after I've run the purge, the return result on the date is the same. I also get only one date returned.

Gauge Gonzalez

7/6/2012 06:05:54 am

When I run select business_date from micros.trans_dtl order by 1 asc, though, I do get many lines returned for very old dates

MicrosPosTools

7/6/2012 07:01:53 am

try running:

select count(*) from micros.trans_dtl

Make a note of the number returned.

Run the purge and then run the above count sql again.

If the count is less after the purge than before then it is working. You may just have a very large amount of old detail in the database and it is taking a long time to purge. It may require you to run the purge alot.

Another thing to check is if you have open checks from back in 2009.

Run:
select * from micros.chk_dtl where chk_open = 'T' order by chk_open_date_time asc

Are there any entries returned where the open date is years ago?

Gauge Gonzalez

7/6/2012 07:37:17 am

When I run the count check I get 515109, when I check for any open checks, I get only three from this year....I don't know why I have so many old checks in the trans detail.

Gauge Gonzalez

7/6/2012 07:47:20 am

Also, when I run the purge through sql, It doesn't finish. I let it run overnight yesterday and it still hadn't stopped....

MicrosPosTools

7/7/2012 02:22:29 am

515109, jesus thats a serious amount of details...

Is the purge still running now? Its taking a very long time because you have such a massive amount of detail to remove.

I think your best option will be to delete detail manually however you will need dba or micros level access to the database. Be warned this is going to take you a long long time to get rid of all that data.

First delete the data from the micros.dtl table. Replace the date below with the min business date you posted above. Each time you run the command increase the date by a few days until you are up to 14 days from the current date.:

delete all from micros.dtl where trans_seq in (select trans_seq from micros.trans_dtl where business_date < '2012-07-07')
commit

The delete the contents of the micros.trans_dtl table using the above method with the date.

delete all from micros.trans_dtl where business_date < '2012-07-07'
commit

Once you have those two tables up to date the purge should run faster and clear the other detail from the rest of the tables.

Gauge Gonzalez

7/9/2012 01:02:23 am

UPDATE: I ran the sp purge on Friday and it run over the weekend. It did finally finish, but I can't say when. It could have been Saturday, or Sunday. The execution time said only 1.3 hours, but I know it when way longer than that. The end result was a drop in about 15000 tickets, which is normal. I'm just trying to figure out what will take longer, purging with the stored procedure, or the method you prescribe above. What do you think?

microspostools

7/9/2012 07:03:37 am

I couldn't say. Depends on the database, the server and size of the average check.

It's easier to run the purge because its 1 command and you can just leave it to run.

Gauge Gonzalez

7/9/2012 07:59:07 am

Final Update: I've been running the purge stored procedure since this morning. After about 6 hours of running the purge, (With an average of 500 secs each.) I'm now down to 5-10 second runs. It's looking like 20,000 transactions is the magic number when it really speeds up. For any one that has this problem, run the purge in SQL the first time and let it run....and...run...and..run, until its done. My database was up to almost 3 GB, which is unheard of. I had over 500,000 transactions not purged. The first purge took in between 30 and 72 hours. (I don't know exactly because I ran it on the weekend.) My server is Dell Poweredge sc1430 with 2 quad core xeon 1.86 ghz processors with 2 gb or ram. Some people may have to go this route if they do not wan't to clear totals. Microspostools, thanks for your help.

8/3/2012 09:46:21 am

Hello Gauge,

Fighting consequences is taught.

For me it looks like your problem could arise from the checks left opened on system, wrongly configured/not configured end of day postings Autosequence.

To see any checks opened on system run the Employee Open Check report with the date range like:

Start Date : 01/10/2000 End Date: Today or Yesterday (depending if you got at present some real checks in progress)

See if your EOD Autosequences are running without a problem - check the \Micros\Res\POS\Etc\3700d.log file for the words "error" and "fail".

There is some variation of the posting steps required, depending on your Micros 3700 system version and used features like GSS or CC interfaces.

After you purged such wast amount of records from your database you might need to rebuild your database.

In general the best is to backup your database before the rebuild and make sure that the copy is saved to a different location(s).

Talk to your local Micros dealer, as repairing your database might become very time-consuming process if the things go wrong.

Ask them to check your end of night Autosequence(es) and rebuild your database if it is still 3GB in size.

Check Historical Totals-Classes and Descriptors. - The size of your Database depends on these settings and how busy is your place.

Best Regards,

37

Tamara Brown

2/12/2016 11:59:17 am

I was having problems with orders coming up more than once and the register actually cashing out orders on its own. When the register had orders pop up more than once, I had to void them. Wondering if anyone else has had any of these issues. HELP

Gauge Gonzalez

2/12/2016 12:05:06 pm

It sounds to me as if you're having network connectIvins issues.I'd start there first. Check cables, plugs, receptacles, etc. When that's been addressed, for sure, I'd rebuild the database.

tom coleman

8/5/2016 06:02:06 am

how do i close a corrupted open check. when i go to reopen it is says Check Read Failure. Also how do I turn on the function that a waiter cannot cash out if there is an pen check. thanks

microsoft technologies training in chennai link

8/16/2016 03:55:53 am

Buoyancy Technologies is a Premier IT Training & Consultation Institute in Chennai that provides High quality, Job Oriented Training programs for Oracle, Cloud Computing, Java, Web, DBA, Software Testing and Microsoft technologies.

Ershad link

1/25/2019 08:45:23 pm

I need it urgently your support for our hotel possible micros3700 , SQL error

plagiarism check online link

10/9/2016 04:02:59 am

This involves the generation of quality content that brings you out as a professional in your niche and posting them in article submission directories. At the very end of the article will be a resource box with a link pointing towards your site.

IOT TRAINING IN CHENNAI link

12/8/2017 03:36:49 am

This is a comprehensive training course in the Internet of Things. You will learn about the IoT introduction, significance, building your own IoT device, sensors, IoT communication, and security.

Khoir link

3/1/2018 04:58:36 am

I have same problem. and Ihad trird all methode above. but still not working

I have closed all opened Check in chk_dtl
I have Called Purge prosedure.
and when I called select count(*) from micros.trans_dtl, after purged the number still same.
when I call Select * from micros.trans_dtl order by business_date the data from 2013 appear. what should I do?

Abraham

5/15/2018 02:12:29 pm

While runiing the SQl - call micros.sp_purgehistory(); I am getting an error saying unable to find in index 'trans_archive_dtl' for table trans_archive_dtl'

What could be wrong ?

Mpho

3/9/2021 02:05:48 pm

Hi Guys, I am trying to get the customer order history from GSS and I can't seem to be able to find a report that will show me exactly what they have ordered previously whne I check autosequence and reports GSS reports. Can someone please advise what needs to be done to get the report?

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

Leave a Reply.

Author

Archives

Categories