Data file size check

Job details

Name:	Data file size check
Platform:	Sqlserver
Category:	Capacity
Description:	Checks the remaining space for all databases where the data files are set with limited growth rate (max size not unlimited).
Long description:	Checks the remaining space for all databases where the data files are set with limited growth rate (max size not unlimited).
Version:	2.1
Default schedule:	5 7,12,17 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’ & eng_inst_priv = 0]

Parameters

Name	Default value	Description
ignore databases	model	List of databases (separated by comma) which will be ignored.
warning threshold	90	Percentage value when a warning is triggered.
alarm threshold	95	Percentage value when an alarm is triggered
minimum free space	10000	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 report on the usage of data file sizes across databases, specifically for those with non-unlimited growth settings.
Why: This job is important to ensure that databases do not reach their maximum allocated file size limit without alerting the system administrators. Reaching or exceeding these limits can lead to potential service disruptions or performance degradation, particularly if the database is unable to grow due to space constraints. Monitoring these metrics helps in proactive management and capacity planning.
Manual checking: You can check this manually in the database by issuing these SQL commands:

SELECT db_name(dbid) as "Database",
  groupid as "Group ID",
  count(*) as "File count",
  sum(CONVERT(Decimal(15,2),ROUND(Size/128.000,2))) as "Allocated Space",
  min(case when growth=0 then cast(size/128. as bigint) else cast(maxsize/128. as bigint) end) as "Lowest value of max size",
  sum(CONVERT(Decimal(15,2),ROUND(case when growth=0 then cast(size/128. as bigint) else cast(maxsize/128. as bigint) end,2))) as "Total max size",
  Case when(sum(CONVERT(Decimal(15,2),ROUND(case when growth=0 then cast(size/128. as bigint) else cast(maxsize/128. as bigint) end,2))))=0 then 0 else Cast(ROUND((sum((size/128.))/sum((maxsize/128.)))*100,1) as bigint) end  as "Pct allocated"
FROM sys.sysaltfiles 
where groupid != 0
  and db_name(dbid) not included in ignored databases
  and db_name(dbid) not in (select name from master..sysdatabases where convert (varchar(20), DATABASEPROPERTYEX(name,'Status')) != 'ONLINE')
group by db_name(dbid),groupid
having min(case when growth=0 then cast(size/128. as bigint) else cast(maxsize/128. as bigint) end)!= -1
order by "Pct allocated" desc;

Implementation Details

The implementation involves the creation of a procedure and a table. This procedure, executed as part of a job in dbWatch, actively checks each database and their respective filegroups for the allocated space, used space, and calculates the percentage usage based on parameters defined (e.g., warning threshold, alarm threshold). Alerts are raised if the used space exceeds the thresholds.

The job dependencies include:

Reliance on the SQL procedure and table for executing checks and capturing necessary data.
A separate dependency where space usage information for filegroups gathers data on the allocated space nearing the max file size.

The presentations in the report generated by dbWatch include:

Listing all databases with their filegroups, number of files, allocated space, and usage information.
Specific space utilization per database and filegroup with a detailed view of the current usage and allocation.

Upgrade Considerations

The procedure should handle potential exceptions and provide clear error messages to assist in troubleshooting.
Upgrade scripts handle schema or procedural changes seamlessly.

This job is crucial for maintaining database health, ensuring ample available space for operations, and preventing unexpected outages related to database file size limitations. The automation of monitoring and alerting processes through dbWatch enhances timely intervention and efficient database management.