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.”