...
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.
| Info |
|---|
We do not recommend using SQL Express Edition when running 10 profiles or more |
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 following content into the new file
| Code Block | ||
|---|---|---|
| ||
# 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'
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') |