SQL Error Log Test

This test reports the number and type of errors logged in the SQL server error logs.

Target of the test : A Microsoft SQL server 2005 (or above)

Agent deploying the test : An internal/remote agent

Outputs of the test : One set of results for the Microsoft SQL server being monitored

Configurable parameters for the test

Parameter

Description

Test period

How often should the test be executed.

Host

The IP address of the Microsoft SQL server.

Port

The port number through which the Microsoft SQL server communicates. The default port is 1433.

FilePath

Enter the full path to the log file to be monitored.

ISUTF16

If the error log file to be monitored is encoded with UTF-16, then, set the ISUTF16 flag to Yes. By default, this flag is set to No.

Is Passive

If the value chosen is yes, then the Microsoft SQL server under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.

User

If a Microsoft SQL Server 7.0/2000 is monitored, then provide the name of a SQL user with the Sysadmin role in this text box. While monitoring a Microsoft SQL Server 2005 or above, provide the name of a SQL user with all of the privileges outlined in User Privileges Required for Monitoring Microsoft SQL server.

Password

The password of the specified User.

Confirm Password

Confirm the password by retyping it.

Instance

In this text box, enter the name of a specific Microsoft SQL instance that is to be monitored. The default value of this parameter is “default”. To monitor an Microsoft SQL instance named “CFS”, enter this as the value of the Instance parameter.

Domain

By default, none is displayed in the Domain text box. If the ‘SQL server and Windows’ authentication has been enabled for the server being monitored, then the Domain can continue to be none. On the other hand, if ‘Windows only’ authentication has been enabled, then, in the Domain text box, specify the Windows domain in which the managed Microsoft SQL server exists. Also, in such a case, the User name and Password that you provide should be that of a user authorized to access the monitored SQL server.

ISNTLMV2

In some Windows networks, NTLM (NT LAN Manager) may be enabled. NTLM is a suite of Microsoft security protocols that provides authentication, integrity, and confidentiality to users. NTLM version 2 (“NTLMv2”) was concocted to address the security issues present in NTLM. By default, the isntlmv2 flag is set to No, indicating that NTLMv2 is not enabled by default on the target Microsoft SQL host. Set this flag to Yes if NTLMv2 is enabled on the target host.

SSL

If the Microsoft SQL server being monitored is an SSL-enabled server, then set the ssl flag to Yes. If not, then set the ssl flag to No.

Login Success DD Row Count

For this test to report detailed diagnosis for the Login Success measure, specify a value against this parameter. For example, specifying 10 against this parameter will fetch the top-10 user logins in the last measurement period and display the same in the detailed diagnosis. By default, the value set against this parameter is 0 indicating that this test will not report detailed diagnosis for the Login Success measure, by default.

Timeout

Specify the time duration (in seconds) beyond which this test should time out in the Timeout text box. The default timeout period is 120 seconds.

Use Procedure

By default, this flag is set to No, indicating that be default, the eG agent collects the required metrics from the SQL Error log file of the target Microsoft SQL server. However in environments where the target server is monitored in an agentless manner, this test is capable of using the xp_readerrorlog stored procedure to pull out the required metrics. This stored procedure mandates the "Execute" permission - i.e., you should configure the test with the credentials of a user vested with the "Execute" permission. To enable the use of xp_readerrorlog procedure to pull out the metrics, set this flag to Yes.

DD Frequency

Refers to the frequency with which detailed diagnosis measures are to be generated for this test. For instance, if you set to 1:1, it means that detailed measures will be generated every time this test runs, and also every time the test detects a problem.

Detailed Diagnosis

To make diagnosis more efficient and accurate, the eG Enterprise suite embeds an optional detailed diagnostic capability. With this capability, the eG agents can be configured to run detailed, more elaborate tests as and when specific problems are detected. To enable the detailed diagnosis capability of this test for a particular server, choose the On option. To disable the capability, click on the Off option.

The option to selectively enable/disable the detailed diagnosis capability will be available only if the following conditions are fulfilled:

  • The eG manager license should allow the detailed diagnosis capability
  • Both the normal and abnormal frequencies configured for the detailed diagnosis measures should not be 0.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

DeadLocks

Indicates the number of deadlocks on the Microsoft SQL server since the last measurement period.

Number

The SQL error log will capture deadlock conditions only if trace is enabled on the SQL server. This means that if the value of this measure is 0, it could imply one of the following:

  • Tracing is not enabled, and therefore, the SQL error log is not able to capture any deadlock conditions; if you want, you can enable tracing by runing the following command from the SQL prompt:

    DBCC TRACEON(1222,-1)

  • Tracing is enabled, but no deadlock has occurred. This is a sign of good health.

If this measure reports a non-zero value on the other hand, it is a cause for concern, as it indicates that one/more deadlocks have occurred. In this case, you can use the detailed diagnosis of this test to know more information about the deadlocks.

Informational messages

Indicates the number of informational messages that were captured by the error log during the last measurement period.

Number

Messages with a severity level of 0 to 10 are informational messages and not actual errors.

User errors

Indicates the number of user errors that were captured by the error log during the last measurement period.

Number

The value of this measure indicates the number of errors with a severity level between 11 and 16. Such errors are generated as a result of user problems and can be fixed by the user.

Software errors

Indicates the number of software errors captured by the error log during the last measurement period.

Number

All errors with severity levels 17 to 19 will be counted as software errors.

Severity level 17 indicates that SQL Server has run out of a configurable resource, such as locks. Severity error 17 can be corrected by the DBA, and in some cases, by the database owner. Severity level 18 messages indicate non-fatal internal software problems. Severity level 19 indicates that a nonconfigurable resource limit has been exceeded.

Fatal or system errors

Indicates the number of fatal or system errors experienced by the target Microsoft SQL server during the last measurement period.

Number

Errors with severity levels 20 to 25 are typically categorized as fatal/system.

Severity level 20 indicates a problem with a statement issued by the current process. Severity level 21 indicates that SQL Server has encountered a problem that affects all the processes in a database. Severity level 22 means a table or index has been damaged. To try to determine the extent of the problem, stop and restart SQL Server. If the problem is in the cache and not on the disk, the restart corrects the problem. Otherwise, use DBCC to determine the extent of the damage and the required action to take. Severity level 23 indicates a suspect database. To determine the extent of the damage and the proper action to take, use the DBCC commands. Severity level 24 indicates a hardware problem. Severity level 25 indicates some type of system error.

Warning messages

Indicates the number of warning messages found in the SQL error logs during the last measurement period.

 

 

Other errors

Indicates the number of other errors captured in the Microsoft SQL server during the last measurement period.

Number

 

Login success

Indicates the number of successful user logins captured in the Microsoft SQL server during the last measurement period.

Number

Use the detailed diagnosis of this measure to

File size

Indicates the current size of the log file.

MB

 

Growth rate

Indicates the rate at which the log file had grown during the last measurement period.

MB/sec