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

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:

The presentations in the report generated by dbWatch include:

Upgrade Considerations

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.