Database maintenance in Cayosoft Guardian

Overview

The Database Maintenance Job in Cayosoft Guardian ensures the health and performance of the system database. This job checks for fragmentation, reclaims unused space, and optimizes database performance by executing necessary cleanup tasks.

The following operations are performed by the Database Maintenance Job:

• Reorganizes or rebuilds indexes to reduce fragmentation and improve query performance.
• Ensures logs do not consume excessive space and helps maintain database consistency.

Running Database Maintenance Job

1. Navigate to Guardian > Configuration > Jobs.
2. Locate and select System Job - Storage Maintenance.
3. Open the job properties form.
4. Click Run to execute the job immediately.

5. Specify:
   1. Delay before restarting the service (seconds) (Optional)
   2. Maximum storage maintenance duration (minutes) (Default: 180)

6. Click Start to begin the process.

The Guardian service will restart in maintenance mode, displaying a progress screen during database maintenance, and the page will refresh automatically upon completion.

 

Once maintenance finishes, the Guardian service will restart back to normal operation.

7. Double-click the job.
8. Monitor the Execution History tab for job results and logs.

Configuring the Storage Maintenance Job

To modify the maintenance job settings:

1. Navigate to System Job - Storage Maintenance and open its properties.
2. Ensure Run Database Maintenance is toggled on.

3. Modify Database Maintenances settings by configuring the following thresholds as needed.

   • DB size threshold to shrink the DB (default: 90% of maximum DB size).

   • Rebuild index when fragmentation is above (default: 50%).

   • Reorganize index when fragmentation is above (default: 15%).

4. Click Save to apply the updated settings.

Transaction log maintenance

Cayosoft Guardian configures the SQL Server database to use the Simple recovery model by default. This recovery model is optimized for environments where point-in-time recovery is not required and where ease of maintenance is a priority. In the Simple recovery model, the transaction log is automatically truncated, which helps prevent excessive growth and eliminates the need for separate log backups.

However, in some environments—particularly in organizations with strict audit, compliance, or backup requirements, the recovery model may be changed to Full or Bulk-Logged. These models retain transaction log entries until a log backup is performed, which can cause the transaction log file to grow significantly if not properly maintained.

 

Recommendations:

• Schedule regular transaction log backups to prevent uncontrolled log growth.

• Monitor the size of the .ldf transaction log file.

• Consider reverting to the Simple recovery model if point-in-time recovery is not required.

Additional SQL database maintenance tasks

To ensure stability of the SQL database, additional maintenance tasks should be performed regularly:

1. Use ALTER INDEX REORGANIZE (for minor fragmentation) or ALTER INDEX REBUILD (for high fragmentation).
2. Run DBCC SHRINKDATABASE or DBCC SHRINKFILE to reclaim unused space. This should be done sparingly as it can cause fragmentation.
3. Schedule full, differential, transaction log backups, and test restore procedures regularly to ensure database integrity.

4. Create new archive databases to store historical records. Delete old archive databases if they are no longer needed.

   There are currently no built-in retention rules for archive databases inCayosoft Guardian.

5. Use DBCC CHECKDB to scan for database corruption. Address any reported issues promptly.
6. Monitor log file growth and use DBCC LOGINFO to check virtual log file status. Set up a SQL Agent job to back up and truncate logs periodically.

Recommended tools for database cleanup

Here are some recommendations regarding tools for managing database fragmentation and purging records:

• SQL Server Maintenance Plans - Built-in SQL Server tool for automating index maintenance, backups, and cleanup tasks.
• SQL Server Management Studio (SSMS) – Includes built-in reports for fragmentation and cleanup.
• DBCC Commands - SQL Server’s native commands for checking integrity, shrinking databases, and optimizing indexes.

The Database Maintenance Job in Cayosoft Guardian helps optimize performance and ensure database health. However, additional SQL database maintenance tasks should be performed periodically to prevent fragmentation, manage archives, and maintain data integrity.