Archive Old StockHistory Records - hmislk/hmis GitHub Wiki

Archive Old StockHistory Records

This guide explains how to move very old pharmacy stock-history rows out of the live StockHistory table and into a separate archive table. Doing this keeps the live table small, which makes pharmacy reports faster and reduces the database disk usage.

This page is for system administrators. Pharmacy staff do not need to use this feature.

Why archive old stock history?

Every pharmacy transaction — every sale, transfer, GRN, adjustment — writes a row to the StockHistory table. Over the years this table grows very large. On a busy hospital it can reach hundreds of millions of historical rows, even though only the last 1–2 years are useful for day-to-day reporting.

A large StockHistory table causes:

  • Slow pharmacy reports. Date-range reports scan more rows than necessary.
  • Slow pharmacy transactions. Every new sale or transfer has to update indexes on the table.
  • Wasted disk space. Most of the rows are old audit data no one looks at.

The Archive Old StockHistory Records feature solves this by moving rows older than a chosen date out of the live table and into a sibling archive table called StockHistoryArchive. The old rows are not deleted — they're still in the database, just not in the way of daily work.

Who can do this?

To use this feature you need the Archive Old StockHistory Records privilege.

Ask your super-administrator to assign it to your user via:

Administration → Manage Users → Add Privileges to Users by Role → expand Pharmacy → Pharmacy Adjustment → Archive Old StockHistory Records

Without this privilege the button will not appear on screen.

Where to find the screen

  1. Log in as an administrator.
  2. In the top menu bar, click Administration (the gear icon).
  3. From the dropdown, click Manage Metadata.
    • The Data Administration screen opens with an accordion sidebar on the left.
  4. In the sidebar, expand the Data tab.
  5. Click the Archive Old StockHistory button.

The Archive Old StockHistory Records page now appears on the right side of the screen.

What the screen looks like

The page has two panels side by side:

Left panel — Archive Settings

Field What it means
Cutoff Date Rows created before this date and time will be archived. By default this is set to 2 years before today.
Batch Size How many rows the system moves in one transaction. Default: 2000. A larger batch finishes faster but holds the database busy for longer; a smaller batch is gentler on day-to-day operations.
Max Batches Per Run A safety cap. The system will stop after this many batches even if more old rows exist. Default: 50 (so up to 50 × 2000 = 100,000 rows per run).
Dry Run If ticked, the system only counts how many rows would be archived. It does not move anything. Always start with Dry Run ticked.

Right panel — Last Result

This panel appears only after you have clicked Preview Count or Run. It shows what happened.

There are two action buttons at the bottom of the left panel:

Button What it does
Preview Count Counts how many rows are older than the cutoff date. Moves nothing. Safe to click any time.
Run Starts the archive job using the current settings. Asks for confirmation. Only does real work if Dry Run is unticked.

How to do an archive — step by step

We recommend doing this in three passes: Preview, Dry Run, then Live Run.

Step 1 — Preview the count (always safe)

  1. Open the Archive Old StockHistory Records page (see "Where to find the screen" above).
  2. Check the Cutoff Date. The default (2 years ago) is usually correct. If you want to keep more recent history live, pick an earlier date. If you want to archive more aggressively, pick a later date — but never inside the last 12 months, because reports need that data.
  3. Leave Dry Run ticked.
  4. Click Preview Count.

You will see a green message at the top of the screen like:

1,772,839 row(s) eligible for archival before 2024-05-14

This tells you exactly how many rows will move. Nothing has been moved yet. Write down the number — you'll compare it to the actual run.

Step 2 — Do a small dry run

A dry run executes the same code path as a real archive but does not actually move rows. This catches obvious mistakes (wrong date, no permission, etc.) without risk.

  1. Keep Dry Run ticked.
  2. Click Run. Confirm the dialog.
  3. The right panel Last Result appears with:
    • Mode: Dry run
    • Candidates: (matches the preview count)
    • Archived: 0 (because dry run doesn't move anything)

If this works, you're ready for the real thing.

Step 3 — Live run, a small batch first

For your very first real run, do a small one. This proves the archive table is working and lets you check how long it took.

  1. Untick Dry Run.
  2. Set Max Batches Per Run to 5 (so 5 × 2000 = 10,000 rows max).
  3. Click Run. Confirm the dialog.
  4. Wait. A 10,000-row run usually finishes in a minute or two.

The Last Result panel will show:

  • Mode: Live run
  • Archived: 10,000 (or fewer if you have less than that)
  • Batches Run: 5
  • Duration (ms): how long it took
  • Message: a summary

Now run Preview Count again. The number should have dropped by exactly the number archived. This confirms the rows really did move out of the live table.

Step 4 — Larger runs until done

Once the small run worked, you can do bigger runs.

  • For routine cleanup, leave Max Batches Per Run at the default 50 and click Run again. Each click archives up to 100,000 more rows.
  • For a one-time large cleanup, you can increase Max Batches Per Run to 500 (1 million rows). Expect this to take longer (rough rule: 1 million rows ≈ 10–30 minutes depending on hardware).
  • Repeat Run until Last Result shows Archived: 0 and Candidates: 0. That means there are no more rows older than your cutoff.

Tip: If the Last Result says "batch limit reached; more rows remain", that just means the safety cap stopped the run — there is more work to do. Click Run again to continue.

Setting it up to run automatically

You can also set the archive to run on its own, e.g. once a month, so old rows are cleaned up without anyone having to remember.

  1. Go to Administration → Manage Metadata → Schedules → Scheduled Process Configuration.
  2. Click Add New.
  3. In the Configuration Details panel:
    • Process: Select Archive Old StockHistory Records.
    • Frequency: Pick how often you want it to run (e.g. Month End).
    • Leave Institution, Department, and Site empty (this is a system-wide job).
    • Next Supposed At: pick the date and time you want the first automatic run. A good choice is the end of next month, very late at night when pharmacy traffic is low.
  4. Click Save.

The job will run automatically at the scheduled time using the system-wide retention setting (see "Changing the defaults" below).

Changing the defaults

The default retention is 2 years (730 days). Sites that want a longer or shorter window can change it.

  1. Go to Administration → Manage Institutions → Application Options.
  2. Search for the keys starting with StockHistory Archive:
    • StockHistory Archive - Retention Days — how old a row must be (in days) before it is archived. Default 730.
    • StockHistory Archive - Batch Size — rows per transaction. Default 2000.
    • StockHistory Archive - Max Batches Per Run — safety cap per run. Default 50.
  3. Edit the value, then click Save.

The new value is used both by the manual page and the scheduled job.

Important: Never set the retention to less than 365 days. Most pharmacy reports look back at least one year (annual stock value reports, audit, year-on-year comparisons). Archiving more recent data will make those reports incomplete.

Where do the archived rows go? Can I get them back?

Archived rows live in a table called StockHistoryArchive in the same database. They have:

  • The same original ID they had in StockHistory (so references stay valid).
  • The same column values as the original row.
  • One extra column called ARCHIVEDAT — the timestamp when they were moved.

If you ever need to bring some archived rows back into the live table, this can be done by a developer or DBA with a single SQL command. Archive is reversible. It is not a delete.

If you want to permanently delete very old archived rows (e.g. older than 7 years for legal data-retention reasons), that is a separate cleanup task that should be done by a DBA in agreement with management.

Troubleshooting

"I don't see the Archive Old StockHistory button"

You don't have the privilege. Ask your super-administrator to grant Archive Old StockHistory Records to your user. See "Who can do this?" above.

"Preview Count says 0 rows"

Either:

  • Your Cutoff Date is set to a very old date (so no rows are older than it), or
  • The archive has already run and there are no more old rows to move.

"The Run button doesn't seem to do anything"

Look at the Last Result panel that appears on the right. If Archived: 0, there were no eligible rows. If you also see an error message at the top of the screen in red, copy it and contact the technical team.

"It says 'reached batch limit; more rows remain'"

That's normal for a large cleanup. The safety cap stopped the run before it processed everything. Just click Run again to continue, or increase Max Batches Per Run for the next click.

"Pharmacy reports look different after the archive"

If your reports cover a period that is older than the cutoff date, they will no longer include those rows because they are in the archive. Either:

  • Adjust the report period to stay within the live retention window (recommended for routine work).
  • Ask the technical team to add an "include archived" option to the specific report that needs it.

Quick reference

You want to … Click …
Know how many old rows you have Preview Count (Dry Run ticked)
Test the workflow without changing data Run (Dry Run ticked)
Actually move some rows Run (Dry Run unticked)
Have the system clean up automatically Administration → Manage Metadata → Schedules → add Archive Old StockHistory Records with Month End frequency
Change how old "old" means Administration → Manage Institutions → Application Options → StockHistory Archive - Retention Days

Related pages: