Use this Knowledge Script to monitor the total number of SQL Server user connections. This script raises an event if the total number of SQL Server user connections exceeds the threshold you specify.
NOTE:To run this Knowledge Script, you need public and view server state SQL Server permissions. If you do not have these permissions, the Knowledge Script does not display any error, but the data returned is not complete. To get complete data, you must have these permissions.
SQL Server instance
The default interval for this script is Every 30 minutes.
Set the following parameters as needed:
Description |
How to Set It |
---|---|
General Settings |
|
Job Failure Notification |
|
Raise event if job fails unexpectedly? |
Select Yes to raise an event if the SQLServer_UserConnections job fails unexpectedly. The default is Yes. |
Event severity when job fails unexpectedly |
Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the job fails unexpectedly. The default is 5. |
Raise event if SQL Server login fails? |
Select Yes to raise an event if login to SQL Sever fails. The default is Yes. |
Event severity when SQL Server login fails |
Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the login to SQL server fails. The default is 15. |
Additional Settings |
|
Event Details |
|
Event detail format |
Select the format in which you want to display the event detail. You can select from HTML Table or Plain Text. The default is HTML Table. |
Authentication |
Select the authentication method that you want to use to access SQL Server. You can either select Windows Authentication or SQL Server Authentication. The default is Windows Authentication. |
User name |
Specify the Windows or SQL Server user name that you want to use to access SQL Server. You can specify multiple users separated by a comma. The default is none. For more information on specifying user name, see Specifying the User Name in the Knowledge Script in the Management Guide. For more information on specifying user name, see Section 2.7.3, Specifying the User Name in the Knowledge Script. |
Monitor User Connections |
|
Number of user connections to include in report |
Specify the number of user connections to display in the event detail message. Enter 0 to display all connections. The default is 20 user connections. |
Include results in data details? |
Select Yes to include the results in the data detail message. By default, query results are not included. |
Event Notification |
|
Raise event if number of connections exceeds threshold? |
Select Yes to raise an event if the number of user connections exceeds the threshold. The default is Yes |
Event severity when threshold exceeds |
Set the event severity level, from 1 to 40, to indicate the importance of an event in which the number of user connections exceeds the threshold. The default is 5. |
Threshold -- Maximum number of user connections |
Specify the maximum number of user connections that are allowed before an event is raised. The default is 100 connections. |
Data Collection |
|
Collect data for total number of user connections? |
Select Yes to collect data for charts and reports. If enabled, data collection returns the total number of SQL Server user connections. The default is unselected. |