Maintenance is not always considered when deploying a database; however, we have found that maintenance can be a critical component to maintaining the health of a database. As a general rule, all tables with clustered indexes should be reindexed on a schedule. We recommend that you do this once a week. However, depending on your community's activity, you may want to do this once a month.
Database maintenance scripts
Telligent Community includes a number of database maintenance scripts.
We include the cs_system_dbreindex stored procedure in the Telligent Community codebase. cs_system_dbreindex can be scheduled (via a SQL Job) to reindex clustered indexes.
Table statistics should be updated on days that reindexing does not occur. Again, there is a stored procedure included in the Telligent Community codebase called cs_system_updatestatistics that can be scheduled for this purpose.
We have also found that people generally do not track physical database and log growth over time. Tracking table and index growth over time gives you an idea of how fast your community is growing and can give you an idea of what your disk space needs will be.
Tracking physical database growth can be done with the SQL Server stored procedure sp_spaceused on a table-by-table basis. Additionally, the number of rows per table is a good metric. The number of rows per table provides you with the averages of the growth of your data over time within your community.
We recommend that you schedule a single full-database backup once a week, a differential backup each day (not on full backup days), and log backups for the maximum recovery time you are allotted for your site.
For more information about backup options refer to:
For additional data about backup schedules refer to
Even though older versions of SQL Server are specified, the processes are the same.
The most effective way to determine problems within the database (related to performance) is through SQL Profiler. An exhaustive review of SQL Profiler can be found here.