The SQL Maintenance is an out-of-the box functionality that comes with Microsoft SQL servers.

In a GN4 system, you can use it to take care about general SQL maintenance and also to provide the backup of GN4 database and transaction logs on a secondary SQL server.

The maintenance routine is typically formed by two different elements:

•The transaction log dump, occurring every 15 minutes. The 15-minutes transaction log backup routine backs files up to a folder of your choice. Example: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup. The maintenance itself won't copy the log dump to the secondary server.

•A nightly maintenance which runs at the time of your choice, however, outside the production hours, e.g. 3AM and does the full database backup.

The nightly maintenance routine does the following tasks:

•Back up database to the folder of your choice, e.g. C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup.

•Shrink the database.

•Execute a Miles 33 SQL statement to rebuild table indexes.

•Delete backup history records older than 2 weeks.

•Delete backup files (both transaction logs and database dumps) older than two days.

The system owner is responsible for the server’s maintenance, including but not limited to:

•        Ensuring sufficient disk space is constantly available

•        Back up of the backed-up files on a separate server/device

•        Re-load of SQL dumps in case of server/database failure

•        General SQL system/database health checks

•        Database mirroring/backup for Disaster Recovery (if any).

•        Database mirroring/backup for High Availability (if any).

See also

Additional readings