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 ResourceXpress database is therefore strongly recommended to ensure performance is not affected and storage limits are not reached.

Table of Contents

minLevel	1
maxLevel	2

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 to 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 second, you configure and schedule the job to carry out the maintenance.

...

Table 1.0 lists the database tables that can be maintained with descriptions of the records held in those tables.

Table 1.0 - Target Tables

Table Name	Description	Potential Growth
dbo.ConnectivityLog

dbo

Records Room Screen connectivity events.

BookingSystemConnectivityLog create

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

Each Room Screen profile creates 1 record every 60 seconds.

To calculate the amount of records generated in a 24 hour period you can multiply the number of

desk

room screen devices in your environment by 1440

e.g.

Panel

bgColor	#DEEBFF

60

Qubis

Room Screens would be 60 * 1440 = 86,400 records in a 24 hour period.*

dbo.BookingSystemConnectivityLog

Any Room Screen profile integrated to a 3rd party booking system will have a corresponding Booking System record when an event is written to the ConnectivityLog

Each Room Screen profile with an active 3rd party booking system integration creates 1 record every 60 seconds.

The same formula detailed in the ConnectivityLog table can be used to estimate the number of generated records

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.

The same formula detailed in the ConnectivityLog table can be used to estimate the number of generated records

dbo.Bookings

Local bookings and 3rd party booking system records.

Each booking either created locally in the application or

syncronised

synchronized 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.

dbo.AuditLog

Status

colour	Green
title	v2023.1

Logs the audit information for the admin console

This depends on the changes done in the admin console.

dbo.GraphSubscription

Status

colour	Green
title	v2023.1

Logs Graph subscriptions for every profile. This is used for sending un-subscription request in case if there is a new subscription available for a resource.

This depends on the number of Exchange/Office 365 profiles.

dbo.SchedulerInfo

Status

colour	Green
title	v2023.1

Store the success profiles, failure profiles, total profiles run by scheduler and their updated time.

This depends on the schedule interval. If it is set to 5, then the table has 144 records per day.

dbo.UserLoginHistory

Status

colour	Green
title	v2023.1

Log the user logged-in time, IP address, Device Type, Login Type and OS Version.

This depends on the number of users logged in to Kiosk, Maps, Admin Console, Mobile, Outlook Add-In.

Note
*Records are not generated when the profile is in Energy Saving Mode

Available Maintenance Actions

...

Deleting will improve performance and reduces reduce the size of the database.

...

Table 1.2 shows the relevant SQL column names and the value values configured by default.

Table 1.1 - SQL Values

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 Names and Default Values

Column Name	Default Value
ConnectivityLogDaysToKeep

0 =

We recommend that at least 1 day of Connectivity Log Records are kept

0

Status

title	Delete all records

BookingSystemConnectivityLogDaysToKeep

0 =

We recommend that at least 1 day of Booking System Connectivity Log Records are kept

0

Status

title	Delete all records

QubiActivityLogDaysToKeep

1

=

Status

title	Archive for 1 day

BookingDaysToKeep

NULL

=

Status

title	Take no action

ResourceMessagesDaysToKeep

NULL

=

Status

title	Take no action

EquipmentProblemsDaysToKeep

NULL

=

Status

title	Take no action

ConnectivityLogArchiveDaysToKeep

0

= Delete all records

Status

title	Take no action

BookingSystemConnectivityLogArchiveDaysToKeep

0

= Delete all recordsQubiActivityLogArchiveDaysToKeep

Status

title	Take no action

QubiActivityLogArchiveDaysToKeep

1

Status

title	Archive for 1 day

BookingArchiveDaysToKeep

NULL

Status

title	Take no action

ResourceMessagesArchiveDaysToKeep

NULL

Status

title	Take no action

EquipmentProblemsArchiveDaysToKeep

NULL

Status

title	Take no action

GraphSubscriptionDaysToKeep

Status

colour	Green
title	v2023.1

3 = Archive for 3 days

SchedulerInfoDaysToKeep

Status

colour	Green
title	v2023.1

1 = Archive for 1 day

BookingArchiveDaysToKeep

AuditLogDaysToKeep

Status

colour	Green
title	v2023.1

NULL = Take no action

AuditLogArchiveDaysToKeep

Status

colour	Green
title	v2023.1

NULL = Take no action

ResourceMessagesArchiveDaysToKeep

UserLoginHistoryDaysToKeep

Status

colour	Green
title	v2023.1

NULL = Take no action

EquipmentProblemsArchiveDaysToKeep

UserLoginHistoryArchiveDaysToKeep

Status

colour	Green
title	v2023.1

NULL = Take no action

Note
Please ensure you take a full database backup before executing any SQL queries

...

Code Block

language	sql

update dbo.ArchiveSetting set <column_name> = <value> where TenantId = 1

Example Queries

Archive Device Connectivity Log Records for 5 days

...

Code Block

language	sql

update dbo.ArchiveSetting set QubiActivityLogDaysToKeep = NULL where TenantId = 1

Delete all Bookings Booking records

Code Block

language	sql

update dbo.ArchiveSetting set BookingDaysToKeep = 0 where TenantId = 1

...

If your ResourceXpress database is hosted on a dedicated SQL Server instance we recommend to carry carrying out database maintenance as a an SQL Agent Job.

To configure maintenance for a SQL Express hosted database please refer to Option 2 - Windows Task Scheduler & PowerShell - SQL Express Edition further down the page.

...

Code Block

language	sql

exec [dbo].ArchiveConnectivityLog 1
GO
exec [dbo].ArchiveBookingSystemConnectivityLog 1
GO
exec [dbo].ArchiveQubiActivityLog 1
GO
exec [dbo].ArchiveBookings 1
GO
exec [dbo].ArchiveResourceMessages 1
GO
exec [dbo].ArchiveEquipmentProblems 1
GO
exec [dbo].ArchiveGraphSubscription 1 
GO 
exec [dbo].ArchiveSchedulerInfo 1 
GO
exec [dbo].ArchiveAuditLog 1 
GO 
exec [dbo].ArchiveUserLoginHistory 1 
GO 
exec [dbo].DeleteBookingsArchive 1 
GO 
exec [dbo].DeleteAuditLogArchive 1 
GO 
exec [dbo].DeleteUserLoginHistoryArchive 1 
GO

...

On the Advanced page drop down the option On success action and select Go to the next step then click OK to save the step

...

Expand the Jobs folder to view the newly created job. If the system has been running for some time you have can run the job immediately instead of waiting for the first scheduled run by right-clicking and selecting Start Job at Step…

...

Create a new text document and name it RxDBMaintenance.ps1

...

Copy the below content into the new file and replace the following values in the $connectionString line

<SQL-SERVER\INSTANCE> = Your SQL Server instance name e.g. SQLSRV01\SQLEXPRESS
<database_name> = your ResourceXpress database name e.g. rxpressexpress

Code Block

language	powershell

# Initial values, replace the {name} with proper values
$connectionString = "Data Source=<SQL-SERVER\INSTANCE-NAME>;Initial Catalog=<database_name>;Integrated Security=False; User ID=dbsupport1;Password=D8Supp0rt!;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"
$timeout = 300 # in seconds
$tenantId = 1

Write-Output('DB Maintenance - v0.1 started')
# Instantiate the connection to the SQL Database
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = $connectionString

# Define the SQL command to run
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = $timeout
$sqlCommand.Connection = $sqlConnection
$sqlCommand.Parameters.AddWithValue("@TenantId",$tenantId)

# Opening the SQL connection
try{
    $sqlConnection.Open()
}
catch{
    Write-Output('An error occurred in opening connection'+ $_.Exception.ToString())
    Exit 1
}

# Executing the stored procedures
$spList = 'ArchiveConnectivityLog','ArchiveBookingSystemConnectivityLog','ArchiveQubiActivityLog','ArchiveBookings','ArchiveResourceMessages','ArchiveEquipmentProblems','DeleteConnectivityLogArchive','DeleteBookingSystemConnectivityLogArchive','DeleteQubiActivityLogArchive','DeleteBookingsArchive','DeleteResourceMessagesArchive','DeleteEquipmentProblemsArchive','DeleteArchiveLog',‘ArchiveGraphSubscription’,’ArchiveSchedulerInfo’,‘ArchiveAuditLog’,’ArchiveUserLoginHistory’,‘DeleteAuditLogArchive’,‘DeleteUserLoginHistoryArchive’

foreach($sp in $spList){
     try{
        Write-Output('About to execute the sp ' + $sp)
        $sqlCommand.CommandText= 'exec [dbo].[' + $sp + '] @TenantId'
		$result = $sqlCommand.ExecuteNonQuery()
        Write-Output($sp + ' executed successfully')
    }
    catch{
        Write-Output('An error occurred in executing the sp ' + $sp + ' ' + $_.Exception.ToString())
    }
}

# Close the SQL connection
$sqlConnection.Close()
Write-Output('DB Maintenance is completed successfully')

Save and close the file.

Open Windows Task Scheduler

...

The new job will be listed in the Task Scheduler Library. If the system has been running for some time you have can run the job immediately instead of waiting for the first scheduled run by right-clicking and selecting Run

...

The number of records deleted and or archived are recorded in the database in the ArchiveLog table, to view the logs execute the below SQL query against the ResourceXpress database.

Code Block

language	sql

select * from dbo.ArchiveLog

...

Document Updates

V2023.3

Two values have been removed from the $spList.

Status
colour Red
title Removed
DeleteGraphSubscription
Status
colour Red
title Removed
DeleteSchedulerInfo

Versions Compared

Old Version 13

New Version Current

Key

Plan and Decide

Table 1.0 - Target Tables

Available Maintenance Actions

Table 1.1 - SQL Values

Table 1.2 - Column Names and Default Values

Example Queries

Document Updates

V2023.3

Page Comparison

Versions Compared

Old Version 13

New Version Current

Key

Plan and Decide

Table 1.0 - Target Tables

Available Maintenance Actions

Table 1.1 - SQL Values

Table 1.2 - Column Names and Default Values

Example Queries

Document Updates

V2023.3