Archiving is the process of transferring data from the production database to an archive database for long-term storage. This helps manage database size and maintain system performance while ensuring historical data is preserved. Data is archived when it becomes inactive or rarely accessed but still needs to be retained for historical, compliance, or analytical purposes. This typically follows a predefined retention policy based on business, legal, or regulatory requirements. Typically, administrators perform archiving tasks, but users with Archive Management Settings permission can configure, manage, and manually trigger the archiving process. Archiving ensures efficient data management by transferring old records to an archive database, reducing clutter in the production system while maintaining historical data for reference.
Archiving is managed from the Archive management page (Admin > Settings > Capacity Management > Archiving).
How Archiving Works
Functionality
Settings can be configured to run an automate archive at selected times or manually at any time by clicking the Run Now button on the Archive Management page.
The Archive Management page does not refresh automatically.
You must click Refresh Grid.
The Custom Reporting Solution (CRS) ETL Process:
Extracts data from Capacity IQ® and loads it into the Custom Reporting Solution™ warehouse.
Requires archiving to be completed first.
Can be run manually from the Archive Management page.
ETL (Extract, Transfer, Load) is the process of extracting data from the Capacity IQ® solution database and loading it into the Custom Reporting Solution™ application warehouse.
It is only applicable if your organization has the Custom Reporting Solution™ application.
If you have the Archive Management Settings permission, you may run the archiving process or the Custom Reporting Solution™ application Extract, Transfer, and Load (ETL) procedure manually at any time.
If the archiving process is not run successfully first, the ETL process will fail, and the following message will appear: Archive has not occurred since last successful ETL.
If you have the Archive Management Settings permission, you may view the following about archiving procedures and about the Custom Reporting Solution™ ETL process on the Archive Management page.
Start and end dates and times.
Statuses (successful, failed, or running).
Custom Reporting Solution (CRS) ETL Audit Report (ETL Control ID)
An Archiving Reset option is available on the Archive Management page.
If you have the Archive Management Settings permission, to configure Archive settings.
The following types of data records are archived when the archiving process is run automatically or manually:
Transport job
Patient
HIPAA
Note: Log records are not archived, but they are permanently deleted from the production database after the number of days selected in the Log Retention Days list on the Archive Management page.
The following permissions are required to archive data and configure related settings:
To access the Capacity Management Administration page, you must have the Admin Tool Menu permission.
To archive data, you must have the Global Admin Permissions-Administrative Controls- Archive Management Settings permission.
You must have Edit rights to make changes.
If you have View rights, you may see, but not change, information.
You must have the Membership Type "All" with administrative rights enabled.
Or, you may have the Membership Type "Fixed" in units or clusters with administrative rights enabled for those units or clusters.
There are two stages to archiving, Synchronization and Purging.
Synchronization ─ Copies all records from the production database to the archive database.
After synchronization is complete, all records that were created or changed since the last archive should be in the archive database.
Records are never removed from the archive database.
Data that was saved less than two minutes before the archiving process was run is not transferred to the archive database.
For example, if a user record is changed and saved at 3:01 AM and then the archiving process is run at 3:02 AM, the changes to the user record are not transferred to the archive database.
Purging ─ Removes archived records from the production database, using the following criteria:
Only transport job, HIPAA, or patient records that are currently in the archive database are deleted from the production database.
Recurring appointment jobs will be deleted in the same manner that other jobs are deleted. The template that is copied to create the individual jobs will not be removed until the end date is past the cutoff date.
HIPAA records are deleted when they are older than the current date minus the number of cutoff days that were selected on the Archive Management page.
Patient records are deleted when:
they are in Discharged or Visit Cancelled status
the last time their status changed is equal to the current date, minus 48 hours, minus the number of cutoff days.
Records will be placed in groups or batches to be deleted, and each batch will be deleted individually.
A lower number of records per batch reduces the effect of the archive process on performance.
Patients in specialty beds can be purged.
Patient records will not be deleted if there are transport jobs associated with them that have not yet been cancelled or completed in the production database.
Patients who have pending transport jobs will not be "unoccupied," or removed from their beds during the archiving process.
Transport jobs are deleted when:
they are in Cancelled or Completed status
the last time their status changed is equal to the current date minus the number of cutoff days.
Older transport jobs that are in Possible status are deleted periodically.
For example, City Hospital requires that for stroke care progression, the Statin Treatment care type must be completed before the Carotid Stenosis Screening care type. The administrator at City Hospital selects Enforce care type order so that Carotid Stenosis Screening cannot be selected on the care type menu unless the Statin Treatment has been selected already. If an HL7 message that would update the patient's care types out of order is received, the message will not be processed, and the care types will not be updated.
If your organization uses Custom Reporting Solution™ application, you will configure settings to run the Extract, Transfer, and Load (ETL) procedure.
When configuring archive settings:
the start time will be based on the time zone selected for the campus that contains your base unit.
For example, if you set the start time for 2:30 AM and the time zone selected for the campus that contains your base unit is U.S. Eastern time, then archiving will start at 2:30 AM U.S. Eastern time.
If the number of failures per process exceeds the number that you selected, the process will not start again until the next scheduled archive or until it is restarted manually.
You can select whether these users receive email each time archiving occurs or only when there are archiving failures.
If the Capacity IQ® database name is changed, but the reference name of Capacity IQ® archive database is not updated to reflect the change, then:
Archiving will be cancelled.
Users who were selected to receive archiving failure emails will receive an email about the cancellation.
The following message will appear on the Archiving History report (accessed from a link in the Archive Batch ID column on the Archive Management page): "Archive batch canceled as archive db reference is not valid, Please contact TeleTracking support immediately."
Example: The Capacity IQ® database is called XT and the archive database is called XTArchive. The databases are restored as XT_1 and XTArchive_1. However, a repair installation has not been run. Therefore, the reference name of the archive database has not been changed to XTArchive_1. If the archive process detects a Capacity IQ® database instance name of XT_1, but the reference to the archive database is XTArchive (and NOT XTArchive_1), then archiving will be cancelled. A repair installation should be run to update the archive database reference name.
Placement Purge Rules:
Placement Request Date Time <= Cutoff Date
Placement should not be the only placement for the patient
Patient Purge Rules
Patient should not have Home or Spec Location ID populated unless populated with Spec Stacking or Admit Holding Stacking location
Patient should not have had any activity (Patient’s last modified date) after the cutoff date
Patient should not have any Pre-Admit placement with a request date time > Cutoff date
Discharged patients will be purged based on Discharge Date Time <= Cutoff date – 2 and Home and Spec null
Visit Canceled patients will be in the system for one day
Patient should not have any transport job activity after the cutoff date (across archive)
Note: In Capacity IQ® PreAdmit, any updates to patient, placement or physician information for a given patient is tracked through ‘PatientInfoLastModDate’ column on the patient table.
This field would be used in patient purge query as the last mod date.
Placements will be purged based on the transaction cutoff date irrespective of whether the related patient is getting purged or not.
Placement Request Date Time criterion has been introduced to take care of old placements that were never closed or canceled.
The latest placement record will be purged only when the patient is purged. It’s acceptable that purging placements before patients would leave some gap in patient’s history.
CBS Purge Logic
ESCt communication with Capacity IQ® EVS is performed using file handshake.
As part of this process three different files named Quota.dat, Refresh.dat, Census.dat will be created.
Out of this ESCt will send Quota.dat and Capacity IQ® will generate Refresh.dat, Census.dat files.
The content of these files will be stored in Capacity IQ® database to display it in the CBS console and also be used for debugging purposes by tech support.
The file data older than the cutoff days (CBSFileRetentionDays) will be purged from Capacity IQ® database.
UnOccupy logic for PreAdmits and Outpatients:
This process will be triggered as a last step of archiving run (irrespective of the archive run outcome)
For PreAdmits: Patient.PatientInfoLastModDate <= cutoff date and Placement.RequestDateTime <= cutoff date
For Outpatients: Patient.PatientInfoLastModDate <= cutoff date
In addition, the patient should also satisfy the following criterion
Patient should not have home location populated with a “Home” location type
Patient should not have had any activity (Patient’s last modified date) after the cutoff date
Patient should not have any transport job activity after the cutoff date (across archive)
Introduce a new master config setting “Auto UnOccupy days for PreAdmit/Outpatient” (cut off days).
Default value: 7 days (Min value allowed: 7 days)
Notes:
UnOccupying these patients should not discharge the patients per the unoccupy-discharge scenario.
Regular archiving logic/process will purge the patient, job data
Only UnOccupy 100 patients in each process run so as to not affect the other regular patient processing
Possible Transport Job Purge Logic
When are Possible Transport Jobs Archived/Purged from Capacity IQ®
Item
Some clients may want to know more information as to how records are purged from the Capacity IQ® Prod database.
In this case, Possible Jobs. The Capacity IQ® Help doesn't really explain the specific details of how or when this happens.
Explanation
The Capacity IQ® Help article on Archiving explains the following regarding purging Possible Transport Job records:
Older transport jobs that are in Possible status are deleted periodically.
This doesn't tell you much as to the exact logic to when these jobs are purged from the Capacity IQ® database.
In testing, we found that possible transport jobs are automatically canceled approximately 48 hours after the associated patient has been discharged.
NOTE: The logic doesn't consider as to when the possible transport job was created.
After the job is canceled, then the logic regarding purging of canceled transport jobs applies.
This is defined in the Capacity IQ® Help article on Archiving as well.
Transport jobs are deleted when:
they are in Canceled or Completed status
the last time their status changed is equal to the current date minus the number of cutoff days.
EXAMPLE: Let's assume the Cutoff Days defined in the Archive Settings is set to 7. Let's assume we discharge a patient on 1/15 @ 8:30pm. On approximately 1/17 @ 8:30pm you will see that the possible transport job is canceled. The possible transport job will then purge out of XT 7 days later on 1/24 during that night's archive process. (1/24 - 7 cut off day = 1/17. The date the possible transport job was canceled)
If an Archive has not run for a number of days, despite being set to run automatically, it may indicate that the archive service is hung.
If Archive or ETL does not run daily, it will cause issues with reports.
Reports will be blank or not run completely
If the Archive process fails with the following error message: “Error Code 802; There is insufficient memory available in the buffer pool,” there is insufficient Memory allocated on SQL server.
If one or more campuses are not shown from the campus drop down list when attempting to run any report from the archive database, it is likely that the user's membership was modified recently. A membership change for certain users requires that user data to be archive which takes a day so the solution is to wait until the next day.
If the user's membership was recently modified, then the campus or campuses that are associated with their membership will not be listed in Standard Reports when selecting the archive database.
Configure Archive Settings
Go to Admin > Settings > Capacity Management > Miscellaneous > Archiving.
To enable archiving, if the Disabled button appears, click it to change it to Enabled.
In the Start Time lists, type the time of day when you would like archiving to start in 24-hour time format (for example, 02:00 for 2 AM or 14:00 for 2 PM).
In the Cut Off Days list, select the number of days after archiving that archived transport job, patient, and HIPAA records should remain in the production database before they are deleted.
In the Log Retention Days list, select the number of days after archiving that log records will remain in the production database before they are deleted.
The archiving process can be set to restart again automatically after a certain number of failures. In the Max Retry list, select the maximum number of failures after which the process will restart automatically.
In the Delete Batch Size list, select the number of records that should be in each batch to be deleted.
In the Notification Email box, type the email addresses of users who should receive email notifications about archiving. Type semi-colons between addresses to separate them (for example, type csmith@abchospital.com;mlopez@abchospital.com).
In the Notify When? list, select one of the following to determine in which circumstances notification will be sent to the addresses listed in the Notification Email box in the Archive Management Settings section:
Always—Email sent each time archiving occurs, regardless of whether it succeeded or failed.
On Failure—Email sent only when archiving fails.
Never—Email never sent.
To save the settings and put them into effect, click Save Settings.
Stop an Archiving Procedure that is Currently in Progress
Click Archiving Reset.
Configure Custom Reporting Solution ETL Procedure Settings
In the CRS Management Settings section of the Archive Management page, to enable the procedure, if the Disabled button appears, click it to change it to Enabled.
In the Notify When? list, select one of the following to determine in which circumstances notification will be sent to the addresses listed in the Notification Email box:
Always—Email sent each time the procedure occurs, regardless of whether it succeeded or failed.
On Failure—Email sent only when the procedure fails.
Never—Email never sent.
The Server Name box displays the name of the server where the procedure places the Capacity IQ® data for use by the Custom Reporting Solution™ application. When troubleshooting, you may check the validity of the database name, server name, and database user name by clicking Test CRS Connection.
Archiving Manually or Running the Custom Reporting Solution™ ETL Procedure Now
After you have configured archiving settings, if the Archive Management page is not displayed, then go to Admin > Settings > Capacity Management > Miscellaneous > Archiving.
To archive manually now, click Run Now.
Before running the Custom Reporting Solution Extract, Transfer, and Load (ETL) procedure, make sure that the archive procedure has run successfully.
To run the Custom Reporting Solution ETL procedure now, click Run CRS ETL Procedure Now.
Viewing Archiving and ETL Procedure History
If the Archive Management page is not displayed, go to Admin > Settings > Capacity Management > Miscellaneous > Archiving.
To refresh the page so that the latest information appears, click Refresh Grid.
View:
Archive Batch ID—The identification code for the archiving procedure.
For troubleshooting purposes, click the batch ID number to display a report with technical information about the archiving procedure.
TeleTracking Technologies’ Client Support may ask you to generate this report to help with troubleshooting.
Archive Status—Whether the archiving procedure was successful, failed, or is running currently.
Archive Start Date and Archive End Date—The dates and times that the archiving procedure started and ended.
ETL Control ID—Clicking this link generates the Custom Reporting Solution (CRS) ETL Audit Report.
ETL Status—Whether the Custom Reporting Solution ETL process was successful, failed, or is running currently.
ETL Start Date and ETL End Date—The dates and times that Custom Reporting Solution ETL process started and ended.
Archive Has Not Run Automatically
The archive service should be recycled.
Confirm that the archive service is set to start automatically overnight by navigating to Admin > Settings > Capacity Management > Archiving.
Once that is confirmed then check for a SQL disconnect or other potential causes for a service hang. A SQL disconnect can be checked for in the event logs (start > run > eventvwr > teletrackinglog).
If a disconnect did occur then restart the Capacity IQ® Archive service along with all other Capacity IQ® services.
Archive Failure Error Code: 802
If the issue continues to occur, then we recommend the DBA increase memory allocations.
Perform the following Index Maintenance:
Index Maintenance should be executed after the XTArchive process is completed to ensure databases have the latest statistics.
Set page size to >= 1000; Have indexes reorg from 10 – 30 % fragmentation; Anything above 30% fragmentation – rebuild indexes; want to run this first to get a clean efficient backup.
Statistics should be updated for Columns and should use a 100% sample size.
A Solutions Engineer should be consulted to confirm SQL maintenance plans are set up properly.
Archive Failure
Go to Admin > Settings > Capacity Management > Archiving
Locate the Archive Status Column.
This will indicate the archive status.
If you see the archive has failed, let the process run again at it’s scheduled time.
If the archive fails consecutively, or has no End date, you will want to contact TeleTracking Client support.