Micros 3700 Pruging checks warning... 1

[ericsdurango]

Hello everyone. Have a question. Managers are telling me that a message keeps popping up on the front terminals saying something about 19,000 checks need to be purged?!. I know that message seems very cryptic, but I have not seen it and the managers cannot seem to write the message down. It seems that this message appears at random times throughout the day. Any ideas out there?
Thanks Eric

 

[afox15]

I have a 3700 system. The check purging is not being done automatically. You need to go into the interactive sql under micros applications and log in. It is usually
login: Custom
password: Custom

in the execute column type call micros.sp_purgehistory

There is a space between call and micros. That should erase 14 days of check data. You will have to do it several times to clear the amount you posted.

 

[pmegan]

Your best bet is to add the purging procedure to the daily maintenance. There should be an autosequence named somthing like "end of day" or "end of night". Most stores will have it scheduled to run automatically at around 4:00-5:00am. If its a 24 hour operation, this will probably be run manually.

Generally this autosequence will rotate the database & 3700d logs, archive logs & journals, purge historical totals, set the new business date, increment employee shifts, etc...

To add purging to the autosequence, open the configurator, go to the Reporting tab and click the Autosequence Steps button. Select the end of night autosequence from the dropdown list and you'll see all the steps it performs. Select an entry and you'll see its details on the right side of the screen.

Select each step that has "true" in the "skip step" column to see if there's already a purging step that's set to be skipped.
If you find Stored Procedure step with Purge Historical Totals in the details, set the "skip step" value to false and you should be done.
If you don't find one, click the blue plus sign to add a step to your autosequence. Change the type to Stored Procedure and select Purge Historical Totals on the right.

Pat

 

[ericsdurango]

afox, thanks for the tip. Any idea how long that stored procedure would take to run? If it only purges two weeks at a a time,seems as if it would not to terribly log, but when I run it, it takes a hell of a long time.
I tried to run the stored procedure again this morning and got this message...unable to find in index 'CFK10TRANS_DTL' for table trans_dtl...
pmegan, I have double checked the autoseq skip step to make sure that step is "false" and it is. That was my first thought.

If I just clear totals, you think this will solve this issue. It is not too big of a deal to clear all totals as we don't really use Micros for historical reporting. What do you think?

Thanks, Eric

 

[pmegan]

Eric,

The time it takes the purge to finish depends on your transaction volumn and the server you have. My company has 14 restaurants all using 3700's. One does about 1500 transactions per day, has a server that's about a year old and takes 2 minutes to purge. Another, (about to be upgraded), does around 6000 transactions per day, is running on a 6 year old HP e800, and take 7-8 minutes to purge. This is only purging one day's worth of transactions, so depending on your system its possible for a 14 day purge to take a half hour. It will also peg the CPU usage, so try to do it when the store is closed.

Purging issues sometimes occur when a check is left open for a very long time. Micros will sometimes purge around checks like this but more often it'll stop at the oldest open check. Run this query in dbisql:

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

This will show all open checks with the oldest on top. If there are checks older than 14 days still open, they'll have to be closed before any purging will happen.

You can also get rid of the "where chk_open = 'T'" line to see how far back your purging will have to go to catch up.


The error you're getting sounds more like your trans_dtl table may be corrupted though. CFK10TRANS_DTL is a foreign key on the trans_dtl table linking the check employee to the employee definition table (emp_def).

Here's what I'd do (on a test pc first if possible):
- Make a backup of the database and put it somewhere outside the micros directories "just in case".
- Rebuild the database.
- Try to purge totals.
- If the purging finished without errors you're back in business. Run the purge until the oldest check in the detail table is 14 days back.
- If not you may have to clear totals.
Either way, when you've either cleared totals or purged everything up to date, rebuild the database. When you have purging issues the transaction tables get bloated with all the unpurged data. Clearing & purging removes the data, but not the allocated space, so you've got a database thats taking up a lot more drive space than the actual data needs.

Rebuilding a full database can take a couple of hours, as can clearing totals. If you rebuild after clearing totals it'll go pretty quick, usually around 20 minutes.

Pat