Saving diagnostics to a trace file

Once enabled, set tracefile saves all SQL text for the current session to the specified file, each SQL text batch appending to the previous batch.

The syntax to enable tracing is:

set tracefile file_name [off] [for spid

The syntax to disable tracing is:

set tracefile off [for spid]

Where:

• file_name – is the full path to the file in which you are saving the SQL text. If you do not specify a directory path, Adaptive Server creates the file in $SYBASE.

  If file_name contains special characters (“:”, “/”, and so on) other than numbers and letters, you must include file_name in quotes. For example, this file_name must be in quotes because of the “/” for the directory structure:

  set tracefile '/tmp/mytracefile.txt' for 25
  

  If file_name does not contain special characters and you want to save it to $SYBASE, it does not require quotes. For example, this file_name does not need to be in quotes:

  set tracefile mytracefile.txt
  

• off – disables the tracing for this session or spid.

• spid – server process ID whose SQL text you want saved to a trace file. Only the users with the SA or SSO role can enable tracing for other spids. You cannot save the SQL text for system tasks (such as the housekeeper or the port manager).

• This example opens a trace file named sql_text_file for the the current session:

  set tracefile '/var/sybase/REL1502/text_dir/sql_text_file'
  

  Subsequent outputs from set showplan, set statistics io, and dbcc traceon(100) are saved in sql_text_file.

• This example does not specify a directory path, so the trace file is saved in $SYBASE/sql_text_file:

  set tracefile 'sql_text_file' for 11
  

  Any SQL run on spid 11 is saved to this tracefile.

• This example saves the SQL text for spid 86:

  set tracefile '/var/sybase/REL1502/text_dir/sql_text_file' for 86
  

• This example disables set tracefile:

  set tracefile off
  

These are the restrictions for set tracefile:

• You cannot save the SQL text for system tasks (such as the housekeeper or the port manager).

• You must have the sa or sso roles, or be granted set tracing permission, to run enable or disable tracing.

• set tracefile is not allowed to open an existing file as a tracefile.

• During an SA or SSO session, if you enable set tracfile for a specific spid, all subsequent tracing commands executed take effect on that spid, not the SA or SSO spid.

• If Adaptive Server runs out of file space while writing the tracefile, it closes the file and disables the tracing.

• If an isql session starts tracing for a spid, but the isql session quits without disabling the tracing, another isql session can begin tracing this spid.

• Tracing occurs for the session for which it is enabled only, not for the session that enabled it.

• You cannot trace more than one session at a time from a single sa or sso session. If you attempt to open a tracefile for a session for which there is already a trace file open, Adaptive Server issues this error message: tracefile is already open for this session.

• You cannot trace the same session from multiple sa or sso sessions.

• The file storing the trace output is closed when the session being traced quits or when you disable tracing.

• Before you allocate resources for tracing, keep in mind that each tracing requires one file descriptor per engine.