# 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 → Preferences → 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 → Preferences → Application Options → **StockHistory Archive - Retention Days** | --- *Related pages:* - [System Administration Functions](System-Administration-Functions) - [Pharmacy Administration](Pharmacy-Administration) - [Administration Navigation Guide](Administration/Administration-Navigation-Guide)