...
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 to carry out database maintenance as a 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.
| Info |
|---|
The SQL Server Agent service must be running to execute the 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
| Code Block | ||
|---|---|---|
| ||
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 |
...
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
| Code Block | ||
|---|---|---|
| ||
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 have can run the job immediately instead of waiting for the first scheduled run by right-clicking and selecting Start Job at Step…
| Note |
|---|
We do not recommend running the job during working hours |