Updated August 29, 2021.
Purpose
To get a baseline of the size of the tables in the databases.
Sources
This feature queries the DMV sys.dm_db_partition_stats which returns page and row-count information for the objects queried.
Reports
The report page Database Size has 2 line charts, one showing the number of rows in the tables, and the other showing the data size, the index size, the reserved space and the unused space in KB.
The x-axis for the charts is at the day level.
The report has 3 buttons to select the level where the tooltips are showing data. For this feature Database, Schema and Object can be selected.
When hovering over the charts tooltips are showing detailed insight by showing the top 5 databases, schemas, or objects (depending on the tooltip button selections) with regards of number of rows, and size in terms of data, index and used space.
Rows tooltip
Database
Schema
Object
Size tooltip
Database
Schema
Object
Model
The model for the report uses the fact [Table size] and the dimensions [Date] and [Object].
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 23:00 and 24:00.
Retention
The default retention for the table dbo.fhsmTableSize is 30 days.
