SQL Server Maintenance Tasks

12d Synergy itself requires very little maintenance. However, as it uses Microsoft SQL Server to manage all its meta data, SQL Server itself has some basic maintenance tasks that are required to ensure good database health.

Index Fragmentation

Indexes are used to speed up how an application works with a SQL Server database. However, over time and as more and more data is written in to database tables, the indexes may become fragmented. Like in disk drive fragmentation, this can have an impact on speed.

It is therefor important to rebuild or reorganise the indexes in your databases regularly. How often you should do this depends on the rate of data being added, but it could be as regularly as once a week or once a month 

Failing to do so can lead to timeouts and other unexpected behaviour.

Index Fragmentation with less than 30 users.

Once a month, you should consider looking into fragmentation. The simplest way to do this is click the "Tune" button that is available in the 12d Synergy Administrator under General→Maintenance→Database.

Index Fragmentation with more than 30 users

At least twice a month, you should consider rebuilding or reorganising your database tables. General practice is to only rebuild or reorganise for indexes >75% fragmentation and more than 500 pages. At this level of 12d Synergy usage, it would be recommended to use the SQL Server Agent to schedule in this operation rather leaving it to a manual operation.

If you have a DBA on staff, they should be consulted for the best strategy.

Transaction Log Truncation

SQL Server has two backup modes - SIMPLE and FULL.

When you use SIMPLE mode, you can only rollback to the last full or differential backup. If you are using SIMPLE mode, you do not have to truncate your transaction log.

When you use FULL mode, SQL Server keeps a full transaction log of all INSERTS, UPDATES and DELETED which can give you to the minute rollback in case of disaster.

This is a very useful tool, but large transaction logs can be slow to write to or to grow.

If you are using FULL recovery mode, you must truncate your transaction log or it may lead to timeouts.

Please see the guide on backup best practices, which is available here: