This blog is part of the blog series named SQL Server Monitoring.
Purpose
To get a baseline of the size of the database files and use it with regards of capacity planning.
Sources
This feature queries the DMV sys.database_files which returns data for each of the database files.
Report
The report has in the upper half 2 line charts, showing the allocated size versus the used size for respectively Data in the left line chart and Log files in the right line chart, where the table in the lower half shows the size per database, and with the possibility to drill down to type (data and log) and logical file level.
Model
The model for the report uses the fact [Database size] and the dimensions [Date] and [Database file].
Configuration
The feature accept the parameter @Databases and the syntax is the same that is used in the SQL Server Index and Statistics Management solution made by Ola Hallengren.
The default parameter for the feature is “@Databases = ‘USER_DATABASES, msdb'”
| Value | Description |
|---|---|
| SYSTEM_DATABASES | All system databases (master, msdb, model, and tempdb) |
| USER_DATABASES | All user databases |
| ALL_DATABASES | All databases |
| Db1 | The database Db1 |
| Db1, Db2 | The databases Db1 and Db2 |
| USER_DATABASES, -Db1 | All user databases, except Db1 |
| %Db% | All databases that have “Db” in the name |
| %Db%, -Db1 | All databases that have “Db” in the name, except Db1 |
| ALL_DATABASES, -%Db% | All databases that do not have “Db” in the name |
Scheduling
The default schedule for the feature is once every day between 07:00 and 08:00.
Retention
The default retention for the table dbo.fhsmDatabaseSize is 180 days.
