Certain tables in the ResourceXpress database are updated with new records on a regular basis. Some of these tables store vital application information, such as Bookings records, while others record device and 3rd party booking system activity which can be useful when troubleshooting a problem.
Having too many records in the database can lead to application performance issues, it can also lead to a large quota of storage being consumed by the database. Scheduled maintenance on the Resourcexpress database is therefore strongly recommended to ensure performance is not affected and storage limits are not reached.
Plan and Decide
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.
Table 1.0
Table Name | Description | Potential Growth |
|---|---|---|
dbo.ConnectivityLog | ||
dbo.BookingSystemConnectivityLog | ||
dbo.QubiActivityLog | Connections to the application from a Qubi 2, Qubi 3 or TD-0350 desk device are stored here. | Each of the listed desk devices will create 1 record every 60 seconds. To calculate the amount of records generated in a 24 hour period you can multiply the number of desk devices in your environment by 1440 e.g. 60 Qubis would be 60 * 1440 = 86,400 records in a 24 hour period.* |
dbo.Bookings | Local bookings and 3rd party booking system records. | Each booking either created locally in the application or syncronised from a 3rd party booking system will create 1 record per configured profile. |
dbo.ResourceMessages | Admin configured resource messages that are broadcast to Room Screens and Kiosks | Resource Messages records still persist in the database even after the configured expiry date has passed. If the message is deleted from the admin console it is also removed from the database. |
dbo.EquipmentProblems | Any “Call for Help” reports for faulty equipment or other resources made from Room Screens or Kiosks. | Equipment Problem records persist in the database even when the problem has been closed in the admin console. |
*Records are not generated when the profile is in Energy Saving Mode
Available Maintenance Actions
There are four available actions for each of the above tables
Delete
The delete action will remove all records from the live target table. Records will not be archived and cannot be retrieved.
Deleting will improve performance and reduces the size of the database.
Archive
The archive option will move the records from the live table to an archive table. Records in an archive table cannot be viewed from the application UI but can be queried directly from the database.
When archiving, you specify the number of days worth of records to retain in the archive table.
Archiving will improve performance as the records do not interfere with application lookups however they still take up the same amount of storage space.
Take No Action
No records will be deleted or archived from the live table.
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.
Table 1.1 describes the available actions and their respective SQL values.
Table 1.2 shows the relevant SQL column names and the value configured by default.
Table 1.1
Action Description | SQL Value |
|---|---|
Delete | 0 |
Archive | Any positive number i.e. 1, 2, 3 etc. The number configured will be the number of days records will be archived |
Take No Action | NULL |
Table 1.2
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 |
Please ensure you take a full database backup before executing any SQL queries
To view the currently applied configuration execute the query
select * from dbo.ArchiveSetting
To change the maintenance action on any column you can run the below update query against the ResourceXpress database.
update dbo.ArchiveSetting set <column_name> = <value> where TenantId = 1
Example Queries
Archive Device Connectivity Log Records for 5 days
update dbo.ArchiveSetting set ConnectivityLogDaysToKeep = 5 where TenantId = 1
Keep all Qubi Activity Log records (not recommended)
update dbo.ArchiveSetting set QubiActivityLogDaysToKeep = NULL where TenantId = 1
Delete all Bookings records
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.