REFRESH TRACING LEVEL

This statement is used to reload the tracing level information from the sa_diagnostic_tracing_level table. It must be called from a database that is being profiled.

When a tracing session is first started, rows from the sa_diagnostic_tracing_level table are loaded into server memory to control what kind of information is traced. To change the types of data being traced without stopping and restarting the tracing session, manually delete or insert the appropriate rows in the sa_diagnostic_tracing_level table, and then execute the REFRESH TRACING LEVEL statement to reload the settings.

To see the current tracing levels, query the sa_diagnostic_tracing_level table as follows:

SELECT * FROM sa_diagnostic_tracing_level WHERE enabled = 1;

Suppose you are troubleshooting a performance problem. You turn on a high level of tracing for the entire database to capture the queries that are causing the problem. After starting the tracing session, you find that capturing all queries for all users on your system slows down your database too much, so you decide you would rather limit tracing to one user and wait for that user to report a problem. However, you do not want to stop the tracing session to change the settings.

You can do this in Sybase Central by using the Database Tracing wizard, which is the recommended method. However, you can also do this from the command line by replacing the rows in sa_diagnostic_tracing_level table where scope=DATABASE and enabled=1, with equivalent rows where scope=USER, identifier=userid, enabled=1, and so on. Then, you execute a REFRESH TRACING LEVEL statement to continue tracing using use the new settings.

You must have the MANAGE PROFILING system privilege.

None.

• ATTACH TRACING statement
• DETACH TRACING statement
• Diagnostic tracing
• sa_diagnostic_tracing_level table

This example refreshes the tracing level:

REFRESH TRACING LEVEL;