Micros xxx checks have been not purged 1

[JosueICR]

I receiving this error every day, i already ran the process call micros.sp_purgehistory(); also did a rebuild to the database, after run the following sentence Select min(business_date) from micros.trans_dtl got records with date 2008

Should i jus delete every record older than 2012 on trans_dtl and chkdtl ? any other table to consider in the manual purge ?


Regards

Josue

 

[pmegan]

You need dba access to delete from the detail tables, but I wouldn't delete records manually, there's just too much that can go wrong.

Approximately how many checks haven't purged? That can make a big difference in the repair work. If you're holding every check since 2008 it'll be in the 10's or even 100's of thousands. This query will tell you how many checks are in the chk_dtl table from each year. If the purge hasn't been running since 2008 you'll have a huge number for each year. If it's been running and you just have a few checks that are stuck you should have low numbers before 2012 and most of the years will be missing.

select year(chk_open_date_time) chk_year, count(*)
from micros.chk_dtl
group by chk_year
order by chk_year

If it turns out that your database just hasn't been purging you'll have to run sp_purgehistory a whole lot of times. It only purges the oldest 10 days each time it runs, so if you really have all the checks from 2008 forward you'll have to run it 36 times per year to get everything up to date. Here's a quick query to find out if the purge is doing anything:

begin
    declare @startCount int;
    declare @endCount int;
    declare @startDate date;
    declare @endDate date;

    select @startCount = count(*), @startDate = min(business_date) from micros.trans_dtl;
    call micros.sp_PurgeHistory();

    select @endCount = count(*), @endDate = min(business_date) from micros.trans_dtl;
    select @startCount, @endCount, @startDate, @endDate;

end

[ol]
[li]If everything is working correctly the results should have @startCount higher than @endCount and @startDate 10 days earlier than @endDate. In this case you'll just have to run the purging sp over and over as I mentioned above.[/li]
[li]If the count changes but @startDate = @endDate you have an old check that's stuck and the purge is working around it. This is usually caused by a check that was closed but held onto it's final workstation assignment. You can find these by running [blue]select * from micros.chk_dtl where chk_open = 'F' and uws_seq is not null[/blue][/li]
[li]If nothing changes your purge isn't working.[/li]
[/ol]

 

[JosueICR]

Hi Pmegan

Thanks for the detailed info you provided, i ran the instructions to my database and nothing changes, i have the followig historical checks, as far as i can see my purge process is not working

chk_year,count(*)
2008,6031
2009,4265
2011,2
2012,16709

Test 2. @startDate = @endDate


Test 3. Running the following sentencte doesn't retrieve results

select * from micros.chk_dtl where chk_open = 'F' and uws_seq is not null


Any other idea how to eliminate the historical detail info older than 15 days ?

Regards


Josue R.