The system runs database maintenance jobs automatically on a daily and weekly basis. The maintenance jobs help to optimize large scale deployments with running bulk operations during off-hours and periodically reorganizing indices on the database tables. The database maintenance runs as standard SQL Server jobs on SQL Server Standard and Enterprise Editions and executed by the Verba Web Application service in case of SQL Server Express Edition (where jobs are not supported). The SQL Server jobs are created during install time by the installer running specific SQL scripts. To run these scripts, the installer requires specific permissions on the SQL Server. For more information see SQL Server requirements.
The built-in maintenance jobs provide the following features:
|Bulk delete records||The action deletes records in a bulk operation where the retention expired or a matching deletion policy is configured. The data management policy which is executing the deletion only marks the records in the database for deletion and the bulk delete job deletes the records ultimately from the database tables.||Daily|
|Move data from temporary tables to the final tables|
The action moves data from temporary tables which usually store data for the current day only to the final tables where the data resides for the long term. The move job runs on the following tables:
The action rebuilds indices where the fragmentation is greater than 30% on all tables
The action reorganizes indices where the fragmentation is greater than 5% on all tables
The action creates new partitions for the next 5 months and merges partitions with small amount of data for the following tables:
Reviewing and monitoring job execution
The system automatically sends notification alerts after job completion and error alerts when a job fails.
To review and monitor the execution, the following database tables can be checked:
- maintenance_log: each maintenance job run has a record in this table
- maintenance_log_section: contains a record for each action for the related job
- maintenance_log_section_detail: detailed information for each executed action for the related job
The following SQL query retrieves the most recent 1000 log entries from the tables:
Changing the job schedule
The maintenance job runs at 22:00 by default (SQL Server local time in case of SQL Server jobs or Verba Media Repository / Application Server local time in case of SQL Server Express). The weekly actions run on Saturday by default.
To change the schedule follow the steps below:
Changing the schedule for SQL Server jobs
Step 1 - Start SQL Server Management Studio and connect to the Verba database
Step 2 - Select SQL Server Agent from the dropdown and expand Jobs
Step 3 - Right-click on the Verba Maintenance job (database_name) and select Properties
Step 4 - Select Schedules on the top left and click on Edit
Step 5 - Change the Daily frequency / Occurs once at parameter to the desired setting
Step 6 - Click Ok and Ok. The SQL Server saves the changes and the job will run based on the new schedule.
Changing the schedule for jobs executed by the Web Application
Step 1 - Login to the Verba Web Application with system administrator privileges
Step 2 - Navigate to System / Server and select the Verba Media Repository / Application Server or navigate to System / Configuration Profiles and select the profile for the Media Repository servers.
Step 3 - Click on the Change Configuration Settings tab and expand to Web Application / Miscellaneous
Step 4 - Under Daily Job Start At (Server Time Zone) select the appropőiate value from the dropdown list.
Step 5 - Save the changes by clicking on the icon.
Step 6 - A notification banner will appear on the top. Click on the click here link, so you will be redirected to the Configuration Tasks tab. Click on the Execute button in order to execute the changes.