Getting the Disk Usage By Table report in SQL Server

Agenda

Approach uno - SQL Server Management Studio report

MSSQL Management studio ships with a set of well-crafted reports that are accessible via right-click on the database in 'Object Explorer'.

The one we'll use is called 'Disk Usage By Table' and provides the number of rows for each table in the database:

Right click -> Export command allows to save it in either Excel or PDF format - very handy.

You can refresh it to get the fresh data in one click.

Approach dos - direct SQL query

The information about user tables is stored in a few system views:

• sys.tables - stores table name, and internal table id
• sys.partitions - stores number of rows
• sys.indexes - glue to join all the needed data
• sys.allocation_units - information about information sizes.

The resulting query combines views and produces following output (red part - useful always, green part - useful once in a lifetime):

Checking actual database files

SQL Server studio -> right-click on the database -> Properties -> Files shall show files metadata:

Database data and Transaction log

Data files

Since file operations are expensive, and space allocation on file system is slow, database tries to expand by large chunks and tends not to return the non-used space.

Removing large batch of data should not make your database file smaller unless AutoShrink is on

Transaction log

Transaction log - stores history of data changes for various purposes (replication, rolling back).

The volume of information written to log depends on the Recovery model used.

Conclusion

Database size is a combination of:

1. Actual data
2. Reserved space to improve the I/O operations
3. The transactional log - recent data changes; volume of data varies by Recovery model

Removing large volume of data will unlikely lead to shrinking of the database file.