Getting age of tracefiles in SAP HANA database

In SAP HANA database, you have diagnosis files that include log and trace files, along with a mixture of other diagnosis, error, and information files. These files can be checked for diagnosis to find errors in SAP HANA database.

In HANA database, trace files are stored separately per host, so to get access to the trace files of a multi-node system you have to check for each host individually.

Finding Trace File Location

To find the location of trace files in SAP HANA, you can run the following SQL statement ?

SELECT * FROM M_DISKS WHERE USAGE_TYPE = 'TRACE';

Accessing Trace Files via Command Line

To access trace files using the command line, you can use cdtrace command. This command changes the directory to the trace file location, making it easier to navigate and examine trace files directly from the filesystem.

Using HANA Studio

You can check details of these files using HANA Studio by navigating to the Administration tab. This provides a graphical interface to view and manage trace files across all hosts in your SAP HANA system.

Getting Age of Trace Files

To determine how old your trace files are, you can use the following SQL query ?

SELECT FILE_NAME, 
       SECONDS_BETWEEN(FILE_MTIME, CURRENT_TIMESTAMP) AS Age_In_Seconds
FROM M_TRACEFILES;

This query returns the file name and the age of each trace file in seconds. The SECONDS_BETWEEN function calculates the difference between the file's modification time (FILE_MTIME) and the current timestamp.

You can also modify the query to get age in different time units ?

SELECT FILE_NAME,
       SECONDS_BETWEEN(FILE_MTIME, CURRENT_TIMESTAMP)/3600 AS Age_In_Hours,
       SECONDS_BETWEEN(FILE_MTIME, CURRENT_TIMESTAMP)/86400 AS Age_In_Days
FROM M_TRACEFILES;

Conclusion

Getting the age of trace files in SAP HANA is essential for database maintenance and troubleshooting. By using the M_TRACEFILES system view with SECONDS_BETWEEN function, you can effectively monitor and manage your trace files across all hosts in your SAP HANA system.