[toc]
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 Server includes a number of database maintenance scripts:
Database reindex
We include the cs_system_dbreindex stored procedure in the Community Server codebase. cs_system_dbreindex can be scheduled (via a SQL Job) to reindex clustered indexes.
Table statistics
Table statistics should be updated on days that reindexing does not occur. Again, there is a stored procedure included in the Community Server codebase called cs_system_updatestatistics that can be scheduled for this purpose.
Database growth
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.
Database backups
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.
Database tracing
The most effective way to determine load on your website is through some simple database traces achieved with SQL Profiler or Windows Perfmon (perfmon.exe). For SQL Profiler, initial traces can use the default settings. From there, you can determine if more precise traces are necessary. Click here for an exhaustive guide to counters that can help track performance of your IIS service.