The SDL Tridion uses two and possibly three different databases. The SDL Tridion Content Manager Database (called Tridion_cm by default) that is used to store all Tridion content, the SDL Tridion Logging Database (called Tridion_cm_log by default) which holds all error and warning messages that the Content Manager server logs and the SDL Tridion Content Delivery Database (called Tridion_broker by default) which stores all dynamic content and metadata information that is available for the Content Broker, Dynamic Linking, Dynamic Content Delivery and Personalization and Profiling on the Presentation Servers..

Maintaining the databases

Database statistics

Because SDL Tridion uses cost-based optimization, tables and indexes must be analyzed regularly. To perform these analyses, do the following:

On Microsoft SQL Server
Use a standard SQL Maintenance plan or run the stored procedure called sp_updatestats.

On Oracle
Gather statistics manually. To do this, run the script AnalyzeStats.sql included on your SDL Tridion R5 installation CD-ROM.

Indexes

Substantial changes to the database, such as a bulk import, a purge using the Purge Tool, or simply changes due to extended use, may require you to rebuild its indexes. Do rebuild the indexes, do the following:

On Microsoft SQL Server
Use a standard SQL Maintenance plan.

On Oracle
Run the script RebuildIndexes.sql included on your SDL Tridion R5 installation CD-ROM. You can also use the scripts PrepareAnalyzeStatsEx.sql and AnalyzeStatsEx.sql to gather extended index statistics, which provide information about the current quality of the index (for example, the number of deleted rows in the index).

PrepareAnalyzeStatsEx.sql creates a table called PLSQL_INDEX_STATS in the current Oracle Schema. This table is used by AnalyzeStatsEx.sql, which gathers index statistics and collects the statistics in a table. You can perform queries against this table such as: 

SELECT * FROM PLSQL_INDEX_STATS ORDER BY NAME;

Data file location and log file location

Both Oracle and Microsoft SQL Server databases use data files to store the actual data in a database and log files for (re)storing transactions. SDL Tridion maintains queue tables in the SDL Tridion Content Manager database. This means that a high number of transactions per second can occur, causing the transaction log file to grow rapidly. To safeguard against problems arising from this, store the data files and log files of a database on separate (physical) hard disks, and back up and truncate the transaction (or archive) log often. For more information, refer to the vendor-specific information about the correct placement of data files and log files, and about backup procedures.

Managing Database Size

SDL Tridion Content Manager Database

Every time a user edits and saves an item in the Content Manager, it creates a new version of that item.

Versions of items allow you to track changes and to roll back to previous versions of an item. These versions are all stored in the database. If items are edited and saved frequently, multiple versions of items may cause the database to fill up quickly.

The Purge Tool, which is part of the SDL Tridion R5 installation, allows you to remove old versions of items from the SDL Tridion Content Manager database, based on criteria such as modification dates or the number of versions you want to keep (see the SDL Tridion R5 Maintenance Guide for more information on the Purge Tool). It is recommended to keep no more than 10 versions per item.

For Workflow process histories and Publish transactions it is not recommended to keep more than 1000 items, purging these lists daily is recommended.

SDL Tridion Logging Database

Depending on the Logging settings in the SDL Tridion MMC Snap-in (see the SDL Tridion R5 Maintenance Guide for more information on the Snap-in), the Content Manager logs some or all error messages and warnings in the SDL Tridion Event Log Database (logging database).

Currently there is no automated process, tool or public API functionality available to purge the SDL Tridion Event Log Database, therefore it is recommended to truncate the EVENTLOG table of the logging database at least once a year. This permanently removes all messages from the database, without a possibility of restoring them. The SDL Tridion Content Manager environment does not need this data (it's only available for viewing purposes), so performing this action more often has no negative impact on the system. Truncating the database table can be done with the following command:

TRUNCATE TABLE EVENTLOG;