Delete Old Log Data Manually| Teradata Vantage - Deleting Old Log Data Manually from System Tables - Advanced SQL Engine - Teradata Database

Before deleting old log data, determine whether you need to retain records of the data set for deletion. For example, while Teradata recommends that you purge log data at least every 90 to 180 days, security policy for some sites may require retention of access log data for a much longer time period.

Use the following procedure when deleting old log data from system tables:

1. If your site requires long term retention of log data, do the following:
   1. Create a duplicate log table in another database using the Copy Table syntax for the CREATE TABLE statement.

      CT DBC.tablename AS databasename.tablename

      For further information on using the Copy Table syntax, see “CREATE TABLE” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

   2. Back up the table to tape storage in accordance with your site back up policy.
      Tip: Match the log backup/deletion schedule to the amount of log data you want to maintain locally in system tables. For example, to maintain 90 days of log data in a system table do the first back up at 120 days, then delete the last 30 days of data in the log. Back up the log data every 30 days after that.
   3. Drop the duplicate table using a DROP TABLE statement.
2. Log on to SQL Administrator as DBADMIN or another administrative user with DELETE privileges on database DBC.
3. In the Query window, enter an SQL statement to purge old log entries.

   For example:

   DELETE FROM DBC.object_name WHERE (Date - LogDate) > number_of_days ;

   object_name

   The name of the DBC object that allows log deletions, as shown in About Database Logs. For example, DeleteAccessLogV is the delete object for the Access Log.

   Date

   SQL syntax element for the current date.

   LogDate

   The date contained in the LogDate column of the log view based on the specified number_of_days from the current date.

   number_of_days

   The number of days of data that you want to retain in the system table; the span of days from the current date to the oldest LOGDATE to be retained.