Local environment variables

You can change the behavior of the Microsoft SQL Server agent by configuring the local environment variables.

Variables for checking availability of the SQL Server service

To check the availability of the SQL Server service, use the following environment variables:

• COLL_MSSQL_RETRY_INTERVAL: This variable provides the retry interval (in minutes) to check the SQL Server service status. If the value is less than or equal to zero, then the variable takes the default value of 1 minute.

• COLL_MSSQL_RETRY_CNT: This variable provides the number of retries that the SQL Server agent makes to check whether the SQL Server service is started or not. If the SQL Server service is not started after the number of retries that are specified in this variable, then collector stops working. If the value of the variable is less than or equal to zero, then the variable takes the default value of 3.

Variables for monitoring the SQL Server error log file

To monitor the MS SQL Error Event Details data set, use the following environment variables:

• COLL_ERRORLOG_STARTUP_MAX_TIME: This variable provides the time interval (T) for error collection before the agent starts. The default value is 0 minutes. This variable can take the following values:

T = 0
The agent starts monitoring the error log file from the time the agent starts or is restarted. The agent does not read the errors that were logged in the error log file before the agent was started.

T = 1
The agent monitors the error log file according to the following values that are set for the COLL_ERRORLOG_STARTUP_MAX_EVENT_ROW variable, which is represented by R:

• If R < 0, the agent starts monitoring the error log file from the time the agent starts or is restarted.
• If R = 1, the agent monitors all the errors that are logged in the error log file.
• If R > 1 and the agent is installed for the first time, the agent monitors the error log file until R errors are monitored. If R > 1 and the agent is restarted, the agent monitors all the previously missed R errors.

T > 1
The agent monitors all previous errors that were logged up to T minutes from the time that the agent starts or restarts. The agent monitoring also depends on the following values that you set for the COLL_ERRORLOG_STARTUP_MAX_EVENT_ROW variable:

• If R ≤ 0, the agent starts monitoring the error log file from the time the agent is started or the agent is restarted.
• If R = 1, the agent monitors the error log file for all the errors that are logged up to T minutes.

• If R > 1, the agent does not monitor more than R errors that are logged in last T minutes.

• COLL_ERRORLOG_STARTUP_MAX_EVENT_ROW: This variable provides the maximum number of errors that must be processed when the agent starts. The default value is 0. You can assign following values to this variable:

R = 0
The agent starts monitoring the error log file from the time that the agent starts or restarts. The agent does not read errors that were created in the error log file before the agent was started.

R = 1
The agent monitors the errors that were logged in the last T minutes from the time that the agent starts or restarts.

R > 1
The agent monitors R errors that are logged in the last T minutes.

• COLL_ERRORLOG_MAX_EVENT_ROW: This variable provides the number of error rows. The default value is 50. You can assign following values to this variable:

X = 0
The agent does not display the error logs.

X > 0
The agent displays the X error rows.

• COLL_ERRORLOG_RECYCLE_WAIT: This variable provides the time interval (in seconds) for which the Microsoft SQL Server agent waits before collecting data of the MS SQL Error Event Detail attribute group when the situation on this attribute group is triggered. You can assign a value to this variable in the range of 1 to 30. If the value of this variable is less than zero, then the variable takes the default value of zero (seconds). If the value of this variable is greater than 30, then the variable takes the default value of 30 (seconds).

Variable for setting the query timeout interval

To set the query timeout interval for the SQL Server agent, use the following environment variables:

• QUERY_TIMEOUT: This environment variable defines the maximum amount of time (in seconds) that the SQL Server agent waits to receive a response for a query that is sent to the SQL Server. The value for this variable must be less than 45 seconds.

However, if you set the value for this variable as 0 seconds, the SQL Server agent waits indefinitely to receive a response from the SQL Server.

If the SQL Server agent accesses many locked databases, you must assign the value to this variable in the range of 10 - 20 seconds. If the query is not processed within the set timeout interval, the SQL Server agent skips the timed out query and moves to the next query in the queue.

The agent does not display data for the query that timed out.

• QUERY_THREAD_TIMEOUT: This environment variable defines the maximum amount of time (in seconds) that the SQL Server agent waits to receive a response for a query that is sent to the SQL Server. This environment variable is applicable for few attribute groups that uses threaded collection. For example, KOQDBD, KOQTBLD, KOQDEVD, and so on.

The value for this variable does not have any limit unlike QUERY_TIMEOUT variable. Otherwise, it works similar to QUERY_TIMEOUT variable.

Variable for viewing information about the enabled jobs

To view the information about enabled jobs in the MS SQL Job Detail data set, use the COLL_JOB_DISABLED environment variable.
If you set the value of this variable as 1, the Microsoft SQL Server agent does not display information about disabled jobs.

If you do not specify this variable, you can view information that is about enabled and disabled jobs.

Variable for limiting the rows in the MS SQL Filegroup Detail data set

To limit the number of rows that the collector service fetches for the MS SQL Filegroup Detail data set, use the COLL_KOQFGRPD_MAX_ROW environment variable. This environment variable defines the maximum number of rows that the collector service fetches for the Filegroup Detail data set.

If you do not specify a value for this variable, the collector service fetches 10,000 rows for the Filegroup Detail data set.
Use this environment variable to modify the default limit of maximum rows in the koqcoll.ctl file. Complete the following steps to modify the default limit:

1. Specify the maximum number of rows for KOQFGRPD in the koqcoll.ctl file.
2. Add the COLL_KOQFGRPD_MAX_ROW environment variable, and ensure that the value of this variable is the same as the value that you have specified in the koqcoll.ctl file.

If the value in the koqcoll.ctl file is less than the value that is specified in the COLL_KOQFGRPD_MAX_ROW environment variable, the value in the koqcoll.ctl file is treated as the value for the maximum number of rows.

If the value in the koqcoll.ctl file is greater than the value that is specified in the COLL_KOQFGRPD_MAX_ROW environment variable, the value in the COLL_KOQFGRPD_MAX_ROW environment variable is treated as the value for the maximum number of rows.

Variables for enhancing the collection for the MS SQL Filegroup Detail data set

Use the COLL_DBD_FRENAME_RETRY_CNT variable to specify the number of attempts that can be made to move the %COLL_HOME%_tmp_%COLL_VERSION%_%COLL_SERVERID%_%COLL_SERVERID%_FGRP_TEMP file to the %COLL_HOME%_tmp_%COLL_VERSION%_%COLL_SERVERID%_%COLL_SERVERID%_FGRP_PREV file.

If you do not specify a value for this variable, the Microsoft SQL Server agent makes 3 attempts to move the file.

Variable for limiting the rows in the MS SQL Device Detail data set

To limit the number of rows that the collector service fetches for the MS SQL Device Detail data set, use the COLL_KOQDEVD_MAX_ROW environment variable. This environment variable defines the maximum number of rows that the collector service fetches for the Device Detail data set.

If you do not specify a value for this variable, the collector service fetches 10,000 rows for the Device Detail data set. Use this environment variable to modify the default limit of maximum rows in the koqcoll.ctl file.

Complete the following steps to modify the default limit:

1. Specify the maximum number of rows for KOQDEVD in the koqcoll.ctl file.
2. Add the COLL_KOQDEVD_MAX_ROW environment variable, and ensure that the value of this variable is the same as the value that you have specified in the koqcoll.ctl file.

If the value in the koqcoll.ctl file is less than the value that is specified in the COLL_KOQDEVD_MAX_ROW environment variable, the value in the koqcoll.ctl file is treated as the value for the maximum number of rows.

If the value in the koqcoll.ctl file is greater than the value that is specified in the COLL_KOQDEVD_MAX_ROW environment variable, the value in the COLL_KOQDEVD_MAX_ROW environment variable is treated as the value for the maximum number of rows.

Variables for enhancing the collection for the MS SQL Device Detail data set

To enhance the MS SQL Device Detail data set collection, use the following environment variables:

• COLL_KOQDEVD_INTERVAL: This environment variable enables you to specify a time interval (in minutes) between two consecutive collections of the MS SQL Device Detail data set.

Note: By default, the data collection for the Device Detail data set is demand based. Use the COLL_KOQDEVD_INTERVAL variable to start a thread based collection for the Device Detail data set and to set the time interval between two threaded collections.

• COLL_DBD_FRENAME_RETRY_CNT: Use this environment variable to specify the number of attempts that can be made to move the %COLL_HOME%_tmp_%COLL_VERSION%_%COLL_SERVERID%_%COLL_SERVERID%_DEVD_TEMP file to the %COLL_HOME%_tmp_%COLL_VERSION%_%COLL_SERVERID%_%COLL_SERVERID%_DEVD_PREV file.

If you do not specify a value for this variable, the Microsoft SQL Server agent makes 1 attempt to move the file.

Variables for enhancing the collection for the MS SQL Database Detail data set

To enhance the MS SQL Database Detail data set collection, use the following environment variables:

• COLL_KOQDBD_INTERVAL: Use this environment variable to specify a time interval (in minutes) between two consecutive thread-based collections of the MS SQL Database Detail data set.

If you do not specify a value for this variable or the specified time interval is less than 3 minutes, then the Microsoft SQL Server agent defaults to 3 minutes interval.

In case, the collection is taking more time or the data is frequently seen as NOT_COLLECTED, then you can check the collection time by referring to the Database Detail Collection completed in %d seconds log and set the variable value to a value that is greater than the collection time specified in the log.

• COLL_DBD_FRENAME_RETRY_CNT: Use this environment variable to specify the number of attempts that can be made to move the %COLL_HOME%_tmp_%COLL_VERSION%_%COLL_SERVERID%_%COLL_SERVERID%_DBD_TEMP file to the %COLL_HOME%_tmp_%COLL_VERSION%_%COLL_SERVERID%_%COLL_SERVERID%_DBD_PREV file.

If you do not specify a value for this variable, the Microsoft SQL Server agent makes 1 attempt to move the file.

Variables for enhancing the collection for the MS SQL Audit Details data set

To enhance the MS SQL Audit Details data set collection, use the following environment variables:

• COLL_AUDIT_TYPE: Use this variable to enable or disable the monitoring of specific logs. The default value of the variable is [AL][FL][SL].

By default, the agent monitors all three types of logs that include the application logs, audit files, and the security logs. The value of the variable includes two character code for each log type:

• [AL] for application logs
• [FL] for audit files
• [SL] for security logs

You can change the value of the variable to disable the monitoring of specific log type. For example, if you specify the value of the variable as [AL][SL] the audit files are not monitored. If no value is specified for the variable, audit details not monitored.

• COLL_AUDIT_DURATION: Use this variable to report the audit events that occurred during the time interval that you specify in this variable.

For example, if you set this variable to 7, the audit events that occurred only in last 7 hours are reported by the Audit Details data set. The default value of the COLL_AUDIT_DURATION variable is 24 hours.

• COLL_AUDIT_COLLECTION_INTERVAL: The threaded collection in the Audit Details data set provides specifications of all the database that are present on the SQL server instance. Use this variable to set the interval for this threaded collection.

For example, if you set this variable to 7, a fresh set of database specifications is extracted from the SQL server instance after every 7 hours. The default value of the COLL_AUDIT_COLLECTION_INTERVAL variable is 24.

Variable for enhancing the collection for the MS SQL Process Detail data set

To enhance the MS SQL Process Detail data set collection, use the COLL_PROC_BLOCK_INTERVAL variable with the following values:

• If COLL_PROC_BLOCK_INTERVAL = 0, the collection for the Blocking Process Duration attribute, and the Blocking Resource Duration attribute is disabled.

• If COLL_PROC_BLOCK_INTERVAL = x, the interval between the two consecutive data collections for the Blocking Process Duration and the Blocking Resource Duration attributes is x minutes.

If the COLL_PROC_BLOCK_INTERVAL variable is not set in the CANDLE_HOME directory, the interval between the two consecutive data collections is three minutes.

Variable for excluding the locked objects from the data collection

If the queries that are sent for the Database Detail, Filegroup Details, Database Mirroring, and Device Detail workspaces take long to execute, use the COLL_DBCC_NO_LOCK variable to run a query with the value WITH (NOLOCK). This variable causes the query not to wait in the queue when an object on which the query is run is locked.

Variable for setting the sorting criteria for the rows returned by the Table Details data set

The rows that are returned by the Table Details data set are sorted in a descending order depending on the value that is set for the COLL_TBLD_SORTBY variable.

The default value for the COLL_TBLD_SORTBY variable is FRAG (fragmentation percent). The valid values are: ROWS (number of rows in a tables), SPACE (space used by the table), and OPTSAGE (the optimizer statistics age of the table).

Variable for enhancing the collection for the MS SQL Problem Detail and Problem Summary data sets

• COLL_ALERT_SEV: Use this variable to set the severity level of the error messages that are displayed in the Problem Detail and Problem Summary data sets. Error messages, which have a severity level that is equal to or greater than the value mentioned in this variable, are displayed in the Problem Detail and Problem Summary data sets.

For example, if you set the value of this variable to 10, the error messages with severity level 10 or greater are displayed in the Problem Detail and Problem Summary data sets. If you do not specify a value for this variable, the error messages, which have a severity level that is equal to or greater than 17, are displayed in the Problem Detail and Problem Summary data sets.

• COLL_SINCE_ERRORLOG_RECY: Use this variable to monitor only the high severity errors in the current ERRORLOG file.

If you do not specify a value for this variable, the value of the variable is 0, which means that for collecting the data, the Problem Summary data set also considers the high severity errors that are read from the previous ERRORLOG file. To monitor only the high severity errors in the current ERRORLOG file, set the value of this variable to 1.

Variables for setting the timeout interval

To set the timeout interval for the Microsoft SQL Server agent, you can use the following environment variables:

• WAIT_TIMEOUT: Use this variable to set the wait timeout interval for the Microsoft SQL Server agent.

If any data set takes more than 45 seconds to collect data, then the agent might hang or situations might be incorrectly triggered.

Check the log for the data sets that take more than 45 seconds to collect the data, and use the WAIT_TIMEOUT variable to increase the wait time between the agent process and the collector process.

• COLL_DB_TIMEOUT: Use this variable to define the wait interval (in seconds) for any request such as running a query on the existing SQL server connection to complete before returning to the application.

If you set this value to 0, then there is no timeout. If you do not specify a value for this variable, the agent waits 15 seconds before returning to the application.

Variables for setting the properties of the collector log files

To set the properties of the collector log files, you can use the following environment variables:

• COLL_WRAPLINES: Use this variable to specify the maximum number of lines in a col.out file. The default value of this variable is 90,000 lines (about 2 MB).

• COLL_NUMOUTBAK: Use this variable to specify the number of backup copies of the collector log files that you want to create. By default, five backup copies of the collector log file are created. The backup file is named *.out. When this backup file is full, the file is renamed to *.ou1 and the latest logs are written in the *.out file. In this manner, for five backup files, the oldest logs are available in the *.ou5 file and the latest logs are available in the *.out file.

• You can create more than five backup copies of the collector log files by specifying one of the following values in the COLL_NUMOUTBAK variable:

  • For less than 10 backup files, specify the number of backup files that you want to create in the COLL_NUMOUTBAK variable. For example, if you specify 9 in the COLL_NUMOUTBAK variable, nine backup files will be created.
  • For more than 9 and less than 1000 backup files, in the COLL_NUMOUTBAK variable, specify the number of backup files preceded by a hyphen. For example, if you specify -352 in the COLL_NUMOUTBAK variable, three hundred and fifty-two backup files will be created.

• COLL_DEBUG: Use this variable to enable full tracing of the collector by setting the value of this variable to dddddddddd (10 times “d”).

Variable for deleting the temporary files

COLL_TMPFILE_DEL_INTERVAL: Use this variable to specify the interval (in minutes) after which the KOQ_<timestamp> temporary files should be deleted.

If you do not specify a value for this variable, the value of the variable is 0, which means that the temporary files must be deleted immediately.

Variable for changing driver used by the MS SQL Server agent

To change the driver that is used by the Microsoft SQL Server agent, use the KOQ_ODBC_DRIVER environment variable. This variable specifies the driver that the Microsoft SQL Server agent uses to connect to the SQL Server.

If you do not specify a value for this variable, then agent uses the ODBC SQL Server Driver as a default driver.

Note: When you specify the Microsoft SQL Server driver, ensure that the driver name is correct and the driver is listed under the drivers’ option in data source (ODBC).

Variable for connecting to an AlwaysOn enabled SQL Server database

KOQ_APPLICATION_INTENT: Use this variable to specify the connection option while connecting to SQL Server.

KOQ_APPLICATION_INTENT option details:

• Readonly: Connection is opened with ApplicationIntent as readonly.
• Readwrite: Connection is opened with ApplicationIntent as readwrite.

When it is set to Readwrite, Microsoft SQL Server agent would not perform any write operations with the connection.

If this variable is not set, the connection is established without ApplicationIntent property.

Note: The driver is specified by the environment variable KOQ_ODBC_DRIVER. If this variable is not set, then the default SQL Server driver is used. If the driver doesn’t support ApplicationIntent, the connection is opened without ApplicationIntent property.