versaSRS Knowledge Base

 

 


Database Maintenance Plans

Article ID: Q731239

Category: FAQ - Knowledge Base

The information in this article applies to:

  • versaSRS
  • Database Performance
  • Application Performance
Database growth is impacting versaSRS performance, causing the application to run quite slowly.
The database has gotten very large due to the high volume of Cases and Audit Logs, due to a lack of indexing accessing these Cases is taking increasingly longer.
Both a Weekly and a Daily Database Maintenance Plan should be created and scheduled to run accordingly.
The Weekly Database Maintenance Plan, as outlined below, should be run initially before setting them both to run on a schedule.

VersaDev recommends the following Daily Database Maintenance plan be created:
These recommended tasks should be added in the order below. VersaDev is not responsible for setting up Maintenance plans (unless negotiated otherwise)

1. Check database integrity

2. Check database size; in this step the business needs to consider defining an alert threshold to ensure that the database never reaches capacity and fails, the below query can be used to achieve this. As a recommendation the DBA should set a warning of at least 15% to ensure that the additional space is made available. - *This option is not available via the wizard.

WITH CTE_DBSIZE AS (
      SELECT
            [size] * 8 As [DBSize]
            , [filename]
      FROM sysfiles
)
SELECT CAST(SUM([DBSize] / 1024.0 / 1024.0) As decimal(5, 2)) As [DBSize (GB)] 
FROM CTE_DBSIZE


3. Backup Database

4. Reorganise Index

5. Update Statistics

6. Clean-up History

7. Maintenance Clean-up


VersaDev recommends the following Weekly Database Maintenance plan be created:
These recommended tasks should be added in the order below. VersaDev is not responsible for setting up Maintenance plans (unless negotiated otherwise)

1. Check database integrity.

2. Check database size; in this step the business needs to consider defining an alert threshold to ensure that the database never reaches capacity and fails, the below query can be used to achieve this. As a recommendation the DBA should set a warning of at least 15% to ensure that the additional space is made available. - *This option is not available via the wizard.

WITH CTE_DBSIZE AS (
      SELECT
            [size] * 8 As [DBSize]
            , [filename]
      FROM sysfiles
)
SELECT CAST(SUM([DBSize] / 1024.0 / 1024.0) As decimal(5, 2)) As [DBSize (GB)] 
FROM CTE_DBSIZE


3. Backup Database

4. Rebuild Index

5. Update Statistics

6. Clean-up History

7. Maintenance Clean-up

Did the information in this article help answer your question?

Yes
No
Did not apply


Last Reviewed: 25/10/2023
Powered By VersaDev Pty Ltd Copyright 2022
Last Reviewed 25/10/2023

Print this page  Print this page
Click link to send to a friend  Send to a friend
Add to Favorites  Add to Favorites

Provided by
VersaDev Support Services