...

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.

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.

Target Tables

The following Database maintenance requires a two-step approach. First, you configure the actions to take for each maintainable table and 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

Available Maintenance Actions

...

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

...

No performance or storage benefits apply to this option.

Configuring Maintenance 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 values configured by default.

Table 1.1 - SQL Values

Table 1.2 - Column Names and Default Values

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

Configuring the Maintenance Job

Option 1 - SQL Agent Job - MSSQL Server Only

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

Connect to the SQL Server using SQL Server Management Studio

Expand SQL Server Agent, right-click the Jobs folder, and select New Job…

...

On the General page populate the Name, Category, and Description fields then click OK

...

Select the Steps page and click New…

...

On the General page add a Step name, select the Type as Transact-SQL script (T-SQL) and ensure the correct Database is selected.

Add the below content into the Command window

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

...

On the Steps page click New… again to create a second step

On the General page add a Step name, select the Type as Transact-SQL script (T-SQL) and ensure the correct Database is selected.

Add the below content into the Command window

exec [dbo].DeleteConnectivityLogArchive 1
GO
exec [dbo].DeleteBookingSystemConnectivityLogArchive 1
GO
exec [dbo].DeleteQubiActivityLogArchive 1
GO
exec [dbo].DeleteBookingsArchive 1
GO
exec [dbo].DeleteResourceMessagesArchive 1
GO
exec [dbo].DeleteEquipmentProblemsArchive 1
GO
exec [dbo].DeleteArchiveLog 1
GO

On the Advanced page drop down the option On success action and select Quit the job reporting success then click OK to save the step

...

The two steps will now be listed

...

Select the Schedules page and click New…

Add a Name and configure the schedule options to your requirements. We recommend running the maintenance job daily at 2:00 am to avoid working hours. Once configured click OK.

...

Select the Notifications page and check the option Write to the Windows Application event log, from the dropdown options select When the job fails.

...

Click OK to save the maintenance job.

ResourceXpress Database Maintenance for MSSQL Server has now been configured.

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

Option 2 - Windows Task Scheduler & PowerShell - SQL Express Edition

SQL Server Agent jobs are not supported on a SQL Express Edition instance, if you’re running SQL Express please use the below steps to configure Database Maintenance.

First, create the application directory shown below on the ResourceXpress Server

C:\Program Files (x86)\QED\RxDBMaintenance

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

# 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

...

In the Actions pane select Create Task…, configure the General Settings as below

...

Move on to the Triggers tab and configure the desired schedule. We recommend running the maintenance job daily at 2:00 am to avoid working hours. Click OK.

...

Click New… in the Actions tab and configure the required options.

The Program/script path should point to PowerShell.exe

In the Add arguments field add -File “C:\Program Files (x86)\QED\RxDBMaintenance\RxDBMaintenance.ps1

Click OK.

...

Configure the Settings tab then click OK to save the task.

...

ResourceXpress Database Maintenance for SQL Express Edition has now been configured.

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

...

Logs

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.

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