The system runs database maintenance jobs automatically on a daily and weekly basisbasis and moves records every 15 minutes from the small temporary tables to the final tables. The maintenance jobs help to optimize large-scale deployments with by running bulk operations during off-hours and periodically reorganizing indices indexes on the database tables. The database maintenance runs as standard SQL Server jobs on SQL Server Standard and Enterprise Editions and is executed by the Verba Web Application service in the 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:
| Actions | Description | Schedule |
|---|---|---|
| Bulk delete records | The action deletes records in a bulk operation where the retention has 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 |
store data for |
15 minutes only to the final tables where the data resides for the long term. The move job runs on the following tables:
|
| Every 15 minutes | |
| Rebuild indexes | The action rebuilds |
indexes where the fragmentation is greater than 30% on all tables |
| Daily |
| Reorganize indexes | The action reorganizes |
indexes where the fragmentation is greater than 5% on all tables |
| Daily | ||
| Extend partitions | The action creates new partitions for the next 5 months and merges partitions with a small amount of data for the following tables:
| Daily |
Reviewing and monitoring job execution
...
The Verba Maintenance SQL Agent Jobs are automatically created in the Primary Replica during the installation. The jobs must be created on the Secondary Replicas manually with the provided update-programs-maintenance-job.sql script. This script must be executed in the Secondary Replicas after an upgrade of the system because new jobs may be createdadded to the product.
The Jobs will be started on the Secondary Replicas too based on the schedule but will do nothing because the job checks the state of the replica and will immediately stop the execution on the Secondary Replicas. This way the jobs will be running on the new Primary Replica after a failover.
...
Step 4 - Under Daily Job Start At (Server Time Zone) select the appropőiate appropriate value from the dropdown list.
...
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.
Advanced Configuration
| Configuration ID | Since | Default | Unit | Description |
|---|---|---|---|---|
| maintenance.transaction_log_size_limit.percent | 9.8.6 | 75 | % | If the Transaction Log size exceeds the configured percentage of its maximum size, then the Maintenance Job will stop. |
| maintenance.transaction_log_size_limit.mb | 9.8.6 | 0 | MB | If greater than zero, then overrides the "maintenance.transaction_log_size_limit.percent" configuration with a fixed size. |
| maintenance.index_rebuild.min_fragmentation | 5 | % | Only those indexes will be rebuilt that are more fragmented than the specified value. | |
| maintenance.index_rebuild.min_page_count | 2000 | Only those indexes will be rebuilt that contain at least the specified number of pages. | ||
| maintenance.index_rebuild.rebuild_min_fragmentation | 30 | % | The indexes that are less fragmented than the specified value will be reorganized instead of rebuilt. | |
| maintenance.index_rebuild.wait_after_every_x_seconds | 9.8.6 | 3600 | seconds | The job will be paused every X seconds to ensure that the transaction log backup works seamlessly. |
| maintenance.index_rebuild.wait_seconds | 9.8.6 | 900 | seconds | Defines how long the job will be paused after every "maintenance.index_rebuild.wait_after_every_x_seconds". |
The configuration can be changed with the pr_set_system_setting stored procedure. For example:
DECLARE
@name VARCHAR(512) = 'maintenance.transaction_log_size_limit.percent' /* TODO replace to the ID of the configuration */
, @value VARCHAR(256) = '50' /* TODO replace to the desired value */
;
EXEC pr_set_system_setting
@p_name = @name
, @p_value = @value
, @p_value_number = 1
, @p_visible = 1
, @p_value_read_only = 0
GO