Use this Knowledge Script to monitor the Locks utilization of SQL Server. This script compares the used number of Locks to the maximum available Locks configured for the SQL Server. This script raises an event if the percentage of Locks that are currently in use exceeds the threshold you set.
Monitors the used number of locks compared to the maximum available locks configured for the SQL Server.
NOTE:To run this Knowledge Script, you need public and view server state SQL Server permissions
If you have not configured the maximum number of Locks in the SQL Server then you cannot use this script to collect data.
SQL Server instance
The default interval for this script is Once every hour.
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_NearMaxLocks 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 Locks Utilization |
|
|
Event Notification |
|
|
Raise event if SQL Server is configured for unlimited available Locks? |
Select Yes to raise an event if you have configured the Locks option in SQL Server properties as unlimited. The default is Yes. |
|
Event severity when SQL Server is configured for unlimited available Locks |
Set the event severity level, from 1 to 40, to indicate the importance of an event in which the Locks option in SQL Server properties is configured as unlimited. The default is 25. |
|
Raise event if Locks utilization exceeds threshold? |
Select Yes to raise an event if the percentage of locks that are currently in use exceeds the threshold. The default is Yes. |
|
Event severity when Locks utilization exceeds threshold |
Set the event severity level, from 1 to 40, to indicate the importance of an event in which the percentage of Locks exceeds the threshold. The default is 5. |
|
Threshold - Maximum percentage of Locks utilized |
Specify the maximum percentage of locks that can be in use before an event is raised. The default is 95%. |
|
Data Collection |
|
|
Collect data for Locks utilization? |
Select Yes to collect data for the percentage of locks that are currently in use for charts and reports. The default is unselected. |