BizTalk360 database eventually can grow due to various factors based on your configurations. In that scenario, there is a chance that database fragmentation will increase, which will slow down the SQL Query execution of the BizTalk360 database. The database maintenance feature will give insight into the health of the BizTalk360 database and also help the user to Rebuild or Reorganize the BizTalk360 database table indexes when it reaches the threshold limit. This action is handled via a sub-service called "Database Maintenance" which runs under the BizTalk360 monitoring service.
Threshold configuration
In default, the toggle (Enable Database Maintenance) is enabled. The fragmentation threshold is updated with a default value as below. This default threshold value can be changed as per your database usage and business needs. By increasing the fragmentation threshold value for reorganize, automatically the rebuild values get increased.
- Rebuild Index - Fragmentation threshold (70%) and Page Count (1000)
- Reorganize Index - Fragmentation threshold (50%) and Page count (500)
Based on this threshold value system will list the indexes and their status, whether any actions like rebuilding or reorganizing are required. The BizTalk360 system will automatically take action against fragmented indexes. Users can also create a schedule with multiple timings on daily, Weekly or Monthly basis.
The health of the index is also updated in the header along with the status and number of indexes that are beyond the threshold limit.
Steps to Configure Schedule for Database Maintenance
- Navigate to Settings->BizTalk360 health->Database Maintenance
- Update the threshold value for fragmentation and page count to rebuild the index. The Minimum threshold value is 30 and set the AND\OR condition between the Fragmentation and page count.
- Update the threshold value for fragmentation and page count to reorganize the index. The Minimum threshold value is 30 and set the AND\OR condition between the Fragmentation and page count.
- Enable the schedule to rebuild or reorganize the table indexes and select the frequency (Daily, weekly, and Monthly). Update the required days & times to execute the schedule. So that the Rebuild or Reorganize action will be taken only during the configured time.
- The Send Notification option is used to send the notification if it is enabled, If the option is disabled the alert notification will not be sent to the admin when the rebuild and reorganize are performed.
- Once the execution is completed the "Last execution time" will be updated
In situations where the database maintenance processes are causing unexpected downtime or performance degradation during crucial business hours, temporarily disabling maintenance could be considered to ensure uninterrupted service. So, at that time, you can disable the toggle (Enable Database Maintenance), rebuild, and reorganize will stop performing and configurations get disabled. 
Execution History
The execution history can be viewed in the section next to the Table Index details. This section displays the total number of indexes on which a rebuild or reorganize action was performed, along with the execution start and end times.
By clicking the property icon, you can access detailed index information, including the table name, index name, and the before-and-after values for average fragmentation, page count, and status. This allows you to clearly see the differences and confirm that the rebuild or reorganize action was successfully applied to the violated table indexes, updating their status from Critical to Healthy.
Points to remember
- Rebuild or Reorganize action will be taken offline so the table Indexes that are opted for Rebuild will not be available for I/O operations during that time(only for a second).
- BizTalk360 Monitoring service will take action on indexes immediately once the threshold violation occurs. Enable schedule to perform the Database maintenance in quiet hours.
- It's always advised to use the BizTalk360 purge policy to avoid data growth.