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

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

Parameters Overview

Execution Details

Reporting Output