...
Some planning and decision-making will be required before configuring and executing the database maintenance tasks described below. Consideration should be made on how you consume and report on data in ResourceXpress and how troubleshooting could be affected when records are deleted.
Database maintenance requires a two-step approach. First, you configure the actions to take for each maintainable table and secondly you configure and schedule the job to carry out the maintenance.
Target Tables
Table 1.0 lists the database tables that can be maintained with descriptions of the records held in those tables.
...
No performance or storage benefits apply to this option.
Configuring Maintenence Actions
Configuring the available maintenance actions is carried out in the ResourceXpress database and involves executing SQL queries to set your desired configuration.
...
Column Name | Default Value |
|---|---|
ConnectivityLogDaysToKeep | 0 = Delete all records |
BookingSystemConnectivityLogDaysToKeep | 0 = Delete all records |
QubiActivityLogDaysToKeep | 1 = Archive for 1 day |
BookingDaysToKeep | NULL = Take no action |
ResourceMessagesDaysToKeep | NULL = Take no action |
EquipmentProblemsDaysToKeep | NULL = Take no action |
ConnectivityLogArchiveDaysToKeep | 0 = Delete all records |
BookingSystemConnectivityLogArchiveDaysToKeep | 0 = Delete all records |
QubiActivityLogArchiveDaysToKeep | 1 = Archive for 1 day |
BookingArchiveDaysToKeep | NULL = Take no action |
ResourceMessagesArchiveDaysToKeep | NULL = Take no action |
EquipmentProblemsArchiveDaysToKeep | NULL = Take no action |
| Note |
|---|
Please ensure you take a full database backup before executing any SQL queries |
To view the currently applied configuration execute the query
| Code Block |
|---|
select * from dbo.ArchiveSetting |
To change the maintenance action on any column you can run the below update query against the ResourceXpress database.
| Code Block | ||
|---|---|---|
| ||
update dbo.ArchiveSetting set <column_name> = <value> where TenantId = 1 |
Example Queries
Archive Device Connectivity Log Records for 5 days
| Code Block | ||
|---|---|---|
| ||
update dbo.ArchiveSetting set ConnectivityLogDaysToKeep = 5 where TenantId = 1 |
Keep all Qubi Activity Log records (not recommended)
| Code Block | ||
|---|---|---|
| ||
update dbo.ArchiveSetting set QubiActivityLogDaysToKeep = NULL where TenantId = 1 |
Delete all Bookings records
| Code Block |
|---|
update dbo.ArchiveSetting set BookingDaysToKeep = 0 where TenantId = 1 |
The maintenance actions configured in these columns will be carried out when the maintenance job executes. For example, if you configure the Booking option to 0 (delete) and the maintenance job runs at 2:00 am, all booking records will be deleted at that time.
Once you have configured your desired values you can move on to configuring the maintenance job itself.
Configuring the Maintenance Job
| Table of Contents | ||||
|---|---|---|---|---|
|