Skip to end of banner
Go to start of banner

Database maintenance

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 8 Current »

The system runs database maintenance jobs automatically on a daily basis and moves records every 15 minutes from the small temporary tables to the final tables. The maintenance jobs help to optimize large-scale deployments by running bulk operations during off-hours and periodically reorganizing 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:

ActionsDescriptionSchedule
Bulk delete recordsThe 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 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:

  • section1 -> section2
  • section_meta1 -> section_meta2
  • marker1 -> marker2
  • section_participant1 -> section_participant2
  • section_cdr_media1 -> section_cdr_media2
  • section_file1 -> section_file2
  • section_centera1 -> section_centera2
  • storage_executed_action1 -> storage_executed_action2
  • call_export_log1 -> call_export_log2
  • section_message1 -> section_message2
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:

  • section2
  • section_archived
  • section_message2
  • section_meta2
Daily

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:

SELECT TOP 1000 *
FROM maintenance_log l
INNER JOIN maintenance_log_section s ON s.maintenance_log_id = l.id
INNER JOIN maintenance_log_section_detail d ON d.maintenance_log_section_id = s.id
ORDER BY l.id DESC, s.id DESC, d.id DESC;

Always On Availability Groups

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 too because new jobs may be added 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.

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

Advanced Configuration


Configuration IDSinceDefaultUnitDescription
maintenance.transaction_log_size_limit.percent9.8.675%If the Transaction Log size exceeds the configured percentage of its maximum size, then the Maintenance Job will stop.
maintenance.transaction_log_size_limit.mb9.8.60MBIf 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_seconds9.8.63600secondsThe job will be paused every X seconds to ensure that the transaction log backup works seamlessly.
maintenance.index_rebuild.wait_seconds9.8.6900secondsDefines 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



  • No labels