Data file size check
Job details
Name: |
Data file size check |
Platform: |
Sqlserver |
Category: |
Capacity |
Description: |
This procedure collects space usage in all filegroups defined in each database. The undocumented ‘sp_MSforeachdb’ Stored Procedure is used to execute T-SQL statements against dbo.sysfiles table in every database defined to a SQL Server instance. |
Long description: |
Checks the remaining space for all databases where the data files are set with limited growth rate (max size not unlimited). |
Version: |
1 |
Default schedule: |
90m |
Requires engine install: |
No |
Compatibility tag: |
.[type=‘instance’ & databasetype=‘sqlserver’]/instance[engine_edition = ‘Microsoft SQL Server’]→i/$i[maj_version > ‘1999′ & hasengine =‘NO’ & eng_inst_priv = 0] |
Parameters
Name |
Default value |
Description |
ignore_databases |
360 |
List of databases (separated by comma) which will be ignored. |
warning_threshold |
360 |
The maximum percentage of space usage allowed before a warning is triggered. |
alarm_threshold |
360 |
The maximum percentage of space usage allowed an alarm is triggered. |
minimum_free_space |
360 |
The minimum free space (in MB) left for data file(s) to grow. If the amount of free space falls below this parameter value, and at the same time the amount of free space in percent falls below the “warning/alarm threshold” parameter value, the check will return a WARNING/ALARM. |
Job Summary
- Purpose: The purpose of this job is to monitor and generate alerts for SQL Server database data files that are approaching or exceeding specified usage thresholds based on set maximum sizes.
- Why: This job is important because it helps prevent data-related issues in databases by keeping track of file sizes and usage to avoid running out of allocated space, which can lead to severe performance problems or even system failures. Monitoring thresholds and remaining space allows database administrators to take preventive or corrective action timely.
- Manual checking: You can check the status of the data file sizes manually in the database by issuing this SQL command:
SELECT db_name(s.dbid) db_val, s.groupid gr_id_val, count(*) file_count_val,
sum(CONVERT(Decimal(15,2), s.size/128.)) alloc_sp_val,
min(s.maxsize/128) mm_size_val,
sum(CONVERT(Decimal(15,2), s.maxsize/128.)) tot_size_val
FROM master.dbo.sysaltfiles s
INNER JOIN master.dbo.sysdatabases d on s.dbid = d.dbid
WHERE s.groupid != 0
AND convert(varchar(20), DATABASEPROPERTYEX(d.name,'Status')) = 'ONLINE'
AND convert(varchar(20), DATABASEPROPERTYEX(d.name,'Updateability')) = 'READ_WRITE'
GROUP BY db_name(s.dbid), groupid
HAVING min(maxsize) != -1
Configuration Details
Table: Job Configuration
Property |
Description |
Name |
Data file size check |
Version |
1 |
Company |
dbwatch.no |
Artifact ID |
ms_noschema_data_file_size_check |
Category |
Capacity |
Express Install |
true |
Display Name |
Data file size check |
Long Description |
Checks the remaining space for all databases where the data files are set with limited growth rate (max size not unlimited). |
Compatibility and Installation
- This job is compatible with instances where the database engine is not embedded and targeted specifically for instances running SQL Server with a major version greater than 1999.
- It requires databases that are online and configurable (READ_WRITE).
Parameters Overview
- ignore_databases: This parameter specifies databases that should be excluded from the monitoring process.
- warning_threshold: Specifies the percentage of space usage at which a warning alert is triggered.
- alarm_threshold: Specifies the percentage of space usage at which an alarm alert is triggered.
- minimum_free_space: Defines the minimum remaining space in MB for triggering a warning or an alarm based on usage thresholds.
Execution Details
- This job executes a JavaScript code block that determines the space used, allocates space, and sets the status of the job based on the results. It loops through databases not listed as ignored, checks file group sizes along with their space usage, and generates output properties for detailed reports in dbWatch Control Center.
- Scheduled to run every 90 minutes by default with a timeout of 120 seconds.
Reporting Output
- Provides a detailed report template in dbWatch Control Center showing data file information, which includes file group details with space usage. Separate tables present detailed info and warnings/alarms related to file group space usage under respective headings like “Space usage” and “WARNING/ALARM information.”