|
|
|
SQL Statement logging can be an essential DBA tool for identifying performance issues and unexpected queries. SQL_DEBUG LOG_STMT adds extensive statement logging in sqlserver.log. This information includes connection information, improved timing, and logging the statement before it is actually executed for a detailed audit trail of all SQL operations.
The main advantage compared to SQL_DEBUG_LOG_STUB_MED is that the logging allows identifying which connection sent the statement so that it is easier to understand the statement executed by a specific connection.
Compared to the SQL_DEBUG LOG_STMT_TIMES keyword, this new keyword generates less output and the statement is logged before execution (LOG_STMT_TIMES log after execution). In case of a very long running query, this makes it possible to identify the statement by looking at the log.
|
|
|
This configuration keyword logs executed SQL statements in SYSLOG:
SYSLOG SQL_STATEMENTS
A SYSLOG SQL_STATEMENTS log entry is written after statement execution so it can also include the error code (if any).
The variable part of the SYSLOG entry contains statement information in JSON format similar to SQL_DEBUG LOG_STMT.
Below is a sample showing how it is displayed by the ctalog utility:
Class = 16 (SQL)
Event = 1 (SQL statement)
Date = 09/24/2020
Time = 17:40:11
Sequence number = 37
Error code = -20005
User ID = 'admin'
Node name = 'isql'
Variable-length information:
---------------------------------------------------
{"timestamp":"Tue Sep 24 17:40:27 2020","ipaddr":"127.0.0.1","db":"CTREESQL","user":"admin","thread":29,"statement":"select * from missingtable"}
---------------------------------------------------
