Updated August 29, 2021.
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.
Reports
The report page Database Size has 2 line charts, showing the allocated size versus the used size for respectively Data and Log files.
The x-axis for the charts is at the day level and will in case the feature has executed several times the same day display the latest data for the day.
The report has 2 buttons to select the level where the tooltips are showing data. For this feature Database and Logical name can be selected.
When hovering over the charts tooltips are showing detailed insight by showing the top 5 databases or logical names (depending on the tooltip button selections) with regards of data size and log size.
Data size tooltip
Database
Logical name
Log size tooltip
Database
Logical name
Model
The model for the report uses the fact [Database size] and the dimensions [Database file] and [Date].
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.
