Purging the System Logs - Teradata Database

Purging the System Logs

The system does not automatically purge logs or the tables underlying views such as the AccessLogV, LogOnOffV, and Software_Event_LogV views. Teradata recommends that you copy data off the DBC tables into a separate database, archive the data (as desired), and then delete information from the DBC tables. Some DBC objects to consider purging include:

Resource usage (ResUsage) tables that have logging enabled.

DBQL logs, which include all DBQL tables that have logging enabled.

(The tables DBC.DBQLRuleTbl and DBC.DBQLRuleCountTbl are not part of the log maintenance list. These tables are automatically maintained by the Teradata SQL BEGIN/END QUERY LOGGING statements; an error is returned if you attempt to delete their contents.)

DBC.SW_Event_Log

QCD.DataDemographics. (If you use Query Capture Facility [QCF] with the SQL COLLECT DEMOGRAPHICS statement, you need to explicitly delete rows from this table, DataDemographics, in your user-defined QCD databases.)

Note: Entries in DataDemographics are deleted automatically when you use the INSERT EXPLAIN WITH STATISTICS AND DEMOGRAPHICS statement. For more information, see “Query Capture Facility” in SQL Request and Transaction Processing and “COLLECT DEMOGRAPHICS” in SQL Data Manipulation Language.

Also, the security administrator should purge the logs associated with access logging as well as any other security-related views as needed.In addition to maintaining the size of system tables and logs, you can reduce log file sizes as follows:

Use only DBC.ResUsageSpma instead of activating logging on multiple ResUsage tables. ResUsageSpma may be able to provide all the information you need.

Use roles to manage privileges which help reduce the size of DBC.AccessRights. For other tips on reducing the size of the DBC.AccessRights table, see “Housekeeping on an Ad-Hoc Basis” on page 202.

Track only the information you need for DBQL. The DBQLogTbl may be able to provide all the information you need.

Track only the information you need for accounting.

Use active row filter mode where possible for ResUsage tables.

For example, if you want to keep the DBC.Acctg table from growing too large, use only the ASE variables that report the level of information you need. Using &T or &I for ASE accounting will potentially make the DBC.Acctg table very large; but using &D or &L usually has less impact on disk space and may still provide enough level of detail. For more information, see “Enabling Account String Expansion” on page 160.