Setting Log Level

You can set the severity level of log messages that you want stored in the event table. To do this, set the LOG_LEVEL parameter to a specified level. Messages at this level (and at more severe levels) are ingested into the event table.

LOG_LEVEL is both an object parameter and a session parameter, which means that you can set the parameter on objects and sessions.

Note

You can use handler code to override the log level you set with SQL (as described in this topic) when your handler is written in Python. For more information, see Overriding log threshold levels with Python.

Understanding Log Levels

When you set the LOG_LEVEL parameter to a specified level, only messages at the specified level (and messages at more severe levels) are ingested and made available in the event table.

For example, setting the LOG_LEVEL parameter to WARN means that messages at the WARN, ERROR, and FATAL levels are captured in the event table.

For a list of LOG_LEVEL values and the corresponding levels of messages ingested, see LOG_LEVEL.

Setting the Log Level on an Object

You can set the log level on the following objects:

  • A stored procedure

  • A user-defined function (UDF) or a user-defined table function (UDTF)

  • A database or schema containing procedures and functions

To set the log level on an object, do the following:

  1. Verify that you have the privilege to set the log level on the object.

    You must use a role that is granted or inherits the following privileges:

    • The MODIFY LOG LEVEL global privilege on the account

    • The MODIFY privilege on the object for which you want to set LOG_LEVEL

    For example, to grant the central_log_admin role the privilege to set the log level on any database, schema, stored procedure, UDF, or UDTF in the account (even those for which the central_log_admin role has no other privileges), execute the following statement:

    GRANT MODIFY LOG LEVEL ON ACCOUNT TO ROLE central_log_admin;
    
    Copy

    Note that in order to set the log level on a stored procedure or UDF, the central_log_admin role must also have the USAGE privilege on the database/schema containing the stored procedure or UDF.

    For more information about the MODIFY LOG LEVEL and USAGE privileges, refer to Access control privileges.

  2. Use the ALTER <object> command to set the LOG_LEVEL parameter on the object.

    For the list of levels that you can use, see LOG_LEVEL parameter. As mentioned earlier, messages at the specified level (and messages at more severe levels) are ingested into the active event table.

    For example, to set the log level for a specific UDF, use ALTER FUNCTION to set the LOG_LEVEL parameter for that UDF. As another example, to set the default log level for all functions and procedures in a database, use ALTER DATABASE to set the LOG_LEVEL parameter on that database.

    The following example sets the log level to ERROR for all functions and procedures in the database db. The example overrides this level to WARN for the UDF f1(int).

    USE ROLE central_log_admin;
    
    -- Set the log levels on a database and UDF.
    ALTER DATABASE db1 SET LOG_LEVEL = ERROR;
    ALTER FUNCTION f1(int) SET LOG_LEVEL = WARN;
    
    Copy

    For details on how Snowflake determines the effective log level when the LOG LEVEL is set on different objects, see Understanding How Snowflake Determines the Effective Log Level.

Setting the Log Level for the Current Session

To set the log level for calls to functions and procedures made in the current session:

  1. Verify that you have the privilege to set the log level on the object.

    You must use a role that has been granted the MODIFY SESSION LOG LEVEL global privilege on the account.

    For example, to grant the developer_debugging role the privilege to set the log levels for the current session, execute the following statement:

    GRANT MODIFY SESSION LOG LEVEL ON ACCOUNT TO ROLE developer_debugging;
    
    Copy
  2. Use the ALTER SESSION command to set the LOG_LEVEL parameter for the current session.

    For example:

    USE ROLE developer_debugging;
    
    -- Set the logging level to DEBUG for the current session.
    ALTER SESSION SET LOG_LEVEL = DEBUG;
    
    Copy

If the LOG_LEVEL parameter is set to different levels for the current session and on the functions and procedures called in that session, Snowflake determines the effective log level to use. See Understanding How Snowflake Determines the Effective Log Level.

Understanding How Snowflake Determines the Effective Log Level

You can override the LOG_LEVEL parameter (for both objects and sessions) using a hierarchy of levels. In other words, you can override the LOG_LEVEL set at a higher level in the hierarchy by setting the LOG_LEVEL for an object at a lower level.

The following describes the hierarchy for session and object LOG_LEVEL parameters.

  • For session parameters, the hierarchy is Account » User » Session.

    This means that you can set the parameter for an account, override the account-level parameter for a user, and override the user-level parameter for the current session.

  • For object parameters, the hierarchy is Account » Database » Schema » Object.

    This means that you can set the parameter for an account, override the account-level parameter for a database or schema, and override the database- or schema-level parameter for specific stored procedures and UDFs in that database or schema.

For example, the LOG_LEVEL for a function overrides the LOG_LEVEL for the account that contains the function. If the LOG_LEVEL for the account is FATAL and the LOG_LEVEL for the Java UDF in the account is INFO, the effective LOG_LEVEL is INFO (the level for the function, not the account):

ALTER ACCOUNT SET LOG_LEVEL = FATAL;

ALTER FUNCTION MYJAVAUDF SET LOG_LEVEL = INFO;

-- The INFO log level is used because the FUNCTION MYJAVAUDF
-- is lower than the ACCOUNT in the hierarchy.
Copy

In cases where LOG_LEVEL is set in both the session and object parameter hierarchies, the most verbose LOG_LEVEL is used.

The following table lists examples of how parameters set on the session and object affect the log level used.

Value for the Session

Value for the Object, Schema, Database, or Account

Log Level Used

(unset)

WARN

WARN

DEBUG

(unset)

DEBUG

WARN

ERROR

WARN

INFO

DEBUG

DEBUG

(unset)

(unset)

OFF

For example, the LOG_LEVEL DEBUG overrides the LOG_LEVEL INFO. If the LOG_LEVEL for the session is DEBUG and the LOG_LEVEL for the Java UDF is INFO, the LOG_LEVEL DEBUG for the session overrides the LOG_LEVEL INFO for the UDF. (DEBUG is more verbose than INFO).

ALTER SESSION SET LOG_LEVEL = DEBUG;

ALTER FUNCTION MYJAVAUDF SET LOG_LEVEL = INFO;

-- The DEBUG log level is used because DEBUG is more verbose than INFO.
Copy