SQL Server Maintenance Tasks
- 11 Oct 2023
- 1 Minute to read
- Print
- PDF
SQL Server Maintenance Tasks
- Updated on 11 Oct 2023
- 1 Minute to read
- Print
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
1.1. 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.
1.2. Index Fragmentation
Indexes are used to speed up how an application works with an 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 therefore 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.
1.2.1. Index Fragmentation with less than 30 Users
You should consider performing fragmentation once a month.
You should do this activity out of working hours when there is little or no activity. This procedure will take about 5 minutes.
- In the 12d Synergy Administration application click General tab > Maintenance tab > Database tab.
- Click the Tune Now button.
- Click the Yes button, if you are ready for the procedure.
1.2.2. 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 is recommended to use the SQL Server Agent to schedule this operation rather than doing it manually.
If you have a DBA on staff, they should be consulted for the best strategy.
1.3. Transaction Log Truncation
SQL Server has two backup modes.
- SIMPLE mode - When you use this mode, you can only rollback to the last full or differential backup. You do not have to truncate your transaction log.
- FULL mode - When you use this mode, SQL Server keeps a full transaction log of all INSERTS, UPDATES and DELETES which can give you 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. You must truncate your transaction log or it may lead to timeouts.
Please see the guide on backup best practices, which is available here.
Was this article helpful?