3.12 DBStats

Use this Knowledge Script to monitor the amount of allocated space used by the data and the log files. Separate thresholds are available for the files whose size is set as auto-grow and for those whose size is not set as auto-grow.

If a file is not set as auto-grow, the percentage of used space is compared to the total space allocated for the file. This script then raises an event if the percentage of used space exceeds the threshold you specify.

If a file is set as auto-grow and a maximum file size is specified in the SQL Server, the percentage of used space is compared to the maximum file size is specified in the SQL Server. This script then raises an event if the percentage of used space exceeds the threshold you specify.

If a file is set to auto-grow and maximum file size is configured as 'Unrestricted File Growth' in SQL Server, then no event is raised. However, data collection collects the details for the file size compared to the total database file size allocated.

NOTE:.

To run this Knowledge Script, you need public and read-only permissions 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.

Resource Objects

SQL Server instance or Database folder, if dynamically observing databases. If you are not observing databases dynamically, you can run this script on the Database folder or on individual database objects.

If you run the script on a individual database object, only that object is monitored regardless of how you set the Dynamically observe databases at each interval? parameter.

Default Schedule

The default interval for this script is Once every hour.

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_DBStats 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 Data 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:

  • * 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

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 data space exceeds threshold?

Select Yes to raise an event if the used data space value exceeds the threshold you specify. The default is Yes.

Event severity when used data space exceeds threshold

Set the event severity level, from 1 to 40, to indicate the importance of an event in which the used data space value exceeds the threshold. The default is 5.

Threshold -- Maximum percentage of used data space if database can grow

Specify the maximum percentage of data space that can be used before an event is raised. The percentage usage of data space is compared to the total space available for the database in SQL Server. The default is 90%.

Threshold -- Maximum percentage of used data space if database cannot grow

Specify the maximum percentage of data space that can be used before an event is raised. The percentage usage of data space is compared to the space allocated for the data space, The default is 90%.

Raise event if used data space falls below threshold?

Select Yes to raise an event if the used data space value falls below the threshold you specify. The default is Yes.

Event severity when used data space falls below threshold

Set the event severity level, from 1 to 40, to indicate the importance of an event in which the data space value that is currently in use falls below the threshold. The default is 5.

Threshold -- Minimum used data space

Specify the minimum disk space in MB that is required for the data. If the amount of disk space falls below this threshold, an event is raised.The default is 0 MB.

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 if log space can grow

Specify the maximum percentage of log space in comparison to the maximum log space specified in the SQL Server, that can be used before an event is raised. The default is 90%.

Threshold -- Maximum percentage of used log space if log space cannot grow

Specify the maximum percentage of log space in comparison to the total log space specified in the SQL Server, that can be used before an event is raised. The default is 90%.

Raise event if used log space falls below threshold?

Select Yes to raise an event if the used log space value falls below the threshold you specify. The default is Yes.

Event severity when used log space falls below threshold

Set the event severity level, from 1 to 40, to indicate the importance of an event in which the used log space value that is currently in use falls below the threshold. The default is 5.

Threshold -- Minimum used 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.

Raise event if total used database space exceeds threshold?

Select Yes to raise an event if the total used database space exceeds threshold you specify. The default is Yes.

Event severity when total used database space exceeds threshold

Set the event severity level, from 1 to 40, to indicate the importance of an event in which the total used database space exceeds the threshold. The default is 5.

Threshold- Maximum percentage of total used database space

Specify the maximum percentage of total used database space, that can be used before an event is raised. The default is 90%.

Raise event if total available database space falls below threshold?

Select Yes to raise an event if the total available database space falls below the threshold you specify. The default is Yes.

Event severity when total available database space falls below threshold

Set the event severity level, from 1 to 40, to indicate the importance of an event in which the total used database falls below the threshold. The default is 5.

Threshold- Minimum total database space available

Specify the minimum total database space that is required for the data. If the amount of database space falls below this threshold, an event is raised.The default is 0 MB.

Data Collection

Collect data for data utilization (%)?

Select Yes to collect data for charts and reports. If enabled, data collection returns the percentage of allocated data space that is currently in use. The default is unselected

Collect data for allocated data space (MB)?

Select Yes to collect data for charts and reports. If enabled, data collection returns the data space size in MB that is currently allocated for a specific database. The default is unselected.

Collect data for used data space (MB)?

Select Yes to collect data for charts and reports. If enabled, data collection returns the data space size in MB that is currently in use for a specific database. The default is unselected.

Collect data for log utilization (%)?

Select Yes to collect data for charts and reports. If enabled, data collection returns the percentage of allocated log space that is currently in use. The default is unselected

Collect data for allocated log space (MB)?

Select Yes to collect data for charts and reports. If enabled, data collection returns the log space size in MB that is currently allocated for a specific database. The default is unselected.

Collect data for used log space (MB)?

Select Yes to collect data for charts and reports. If enabled, data collection returns the log space size in MB that is currently in use for a specific database. The default is unselected.

Collect data for total database size (MB)?

Select Yes to collect data for charts and reports. If enabled, data collection returns the size of the total database in MB. The default is unselected.

Collect data for total database space utilization (%)?

Select Yes to collect data for charts and reports. If enabled, data collection returns the percentage of total database space used. The default is unselected.

Collect data for total database space available (MB)?

Select Yes to collect data for charts and reports. If enabled, data collection returns the available database space in MB. The default is unselected