3.10 DBLocks

Use this Knowledge Script to monitor the number of locks per SQL Server database. This script raises an event if the number of locks exceeds the threshold. In addition, this script generates data streams for the number of locks, and you can include a report of locks in the events and data for this script. All the databases in the SQL Server are monitored if dynamic observation of databases is enabled, unless you exclude them.

You can set this script to observe new databases dynamically each time it runs. Observing databases dynamically allows you to monitor locks for newly created SQL Server databases since you ran the Discovery_SQLServer Knowledge Script and prevents you from attempting to monitor databases that have been dropped since discovery.

NOTE:

  • Although this script can observe databases each time it runs, the new databases are not reflected in the Operator Console or Control Center.

  • To run this Knowledge Script, you need public and view server state SQL Server permissions.

  • This Knowledge Script monitors the availability group databases in both primary and secondary replica irrespective of the configuration of the Readable Secondary option of the secondary replica, because this Knowledge Script retrieves the lock information from system.master database.

Resource Objects

System or User Databases

If you are not observing databases dynamically, you can run this script on a Database folder or individual database objects. Dynamic observation monitors all databases regardless of target resource object.

Default Schedule

The default interval for this script is Every 30 minutes.

Setting Parameter Values

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_DBLocks 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 server 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.

Raise event if database is offline?

Select Yes to raise an event if a database is offline. The default is unselected.

Event severity when database is offline

Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when a database is offline. The default is 15.

Raise event if database is deleted?

Select Yes to raise an event if a database is deleted. The default is unselected.

Event severity when database is deleted

Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when a database is deleted. 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 Database Locks

Dynamically observe databases at each interval?

Select Yes to dynamically observe databases at each monitoring interval. The default is unselected.

NOTE:Dynamic observation monitors all databases regardless of target resource object.

Specify list of databases to exclude (comma-separated)

Specify the name of the databases you want to exclude from monitoring, separated by commas.

You can use standard pattern-matching characters when specifying database names:

  • * matches zero or more instances of a previous character

  • ? matches exactly one instance of a previous character

  • \d matches any single digit from 0 - 9

  • [ ] matches exactly one instance of any character between the brackets, including ranges

Specify file path containing list of databases to exclude

Specify the full file path of .csv or .txt format file that contains the name of the databases that you want to exclude from monitoring.

NOTE: Enter each database on a separate line.

The databases specified in the file are excluded even if dynamic monitoring is not enabled.

You can use standard pattern-matching characters when specifying database names:

  • * matches zero or more instances of a previous character

  • ? matches exactly one instance of a previous character

  • \d matches any single digit from 0 - 9

  • [ ] matches exactly one instance of any character between the brackets, including ranges

Include database locks report in events?

Select to Yes to include a report of the number of database locks in the events generated for this script. The default is Yes.

Include database locks report in data?

Select Yes to include a report of the number of database locks in the data for charts and reports. The default is Yes.

Maximum locks to report (0 for maximum)

Set the maximum number of locks you want the script to report on for events and data. The default is 0.

Exclude monitoring availability group databases under SQL Server instance?

Select Yes to exclude monitoring the availability group databases that are under the SQL Server instance. The default is unselected.

Event Notification

Raise event if locks exceed threshold?

Select Yes to raise an event when the number of locks for a database exceeds the threshold. The default is Yes.

Event severity when locks exceed threshold

Set the event severity level, from 1 to 40, to indicate the importance of an event in which the number of locks held exceeds the threshold. The default is 5.

Threshold -- Maximum number of database locks

Specify the maximum number of locks that can be held on a database before an event is raised. The default is 10 locks.

Data Collection

 

Collect data for number of database locks?

Select Yes to collect data for charts and reports. If enabled, data collection returns the number of locks held on a database, and identifies the application and user holding each lock. The default is unselected.