Use this Knowledge Script to monitor a database’s available log space and used log space. This script raises an event if the available log space falls below the threshold you specify, or if the percentage of log space used exceeds the threshold you specify.
You can set this script to observe new databases dynamically each time it runs. Observing databases dynamically allows you to monitor log space 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 new 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 read-only permission on all the databases that are to be monitored.
You can monitor availability group databases on secondary replica only if the Readable Secondary option is configured as Yes for secondary replica.
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.
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_LogSpace 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. |
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 Log Space |
|
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. |
Exclude monitoring loading or restoring databases under SQL Server instance? |
Select Yes to exclude the monitoring of the SQL Server databases that are in loading or restoring state under the SQL Server instance. The default is unselected. |
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:
|
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
|
Exclude monitoring availability group databases under SQL Server instance? |
Select Yes to exclude the monitoring of the availability group databases that are under the SQL Server instance. The default is unselected. |
Event Notification |
|
Raise event if used log space exceeds threshold? |
Select Yes to raise an event if the used log space value exceeds the threshold you specify. The default is Yes. |
Event severity when used log space exceeds threshold |
Set the event severity level, from 1 to 40, to indicate the importance of an event in which the used log space value exceeds the threshold. The default is 5. |
Threshold -- Maximum percentage of used log space |
Specify the maximum percentage of log space that can be used before an event is raised. The default is 90%. |
Raise event if available log space falls below threshold? |
Select Yes to raise an event if the available log space value falls below the threshold you specify. The default is Yes. |
Event severity when available log space falls below threshold |
Set the event severity level, from 1 to 40, to indicate the importance of an event in which the available log space value falls below the threshold. The default is 5. |
Threshold -- Minimum available log space |
Specify the minimum disk space in MB that is required for the database’s log space. If the amount of disk space falls below this threshold, an event is raised.The default is 0 MB. |
Data Collection |
|
Collect data for used log space? |
Select Yes to collect data about the used log space for charts and reports. If enabled, data collection, returns the percentage of log space used for each database. The default is unselected. |
Collect data for available log space? |
Select Yes to collect data about the available log space for charts and reports. If enabled, data collection, returns the available log space in MB. The default is unselected. |