Monday, 16 May 2016

Share with:

This query is particularly handy for generating a report of your database files and how much space they are taking up on disk. You can use this to periodically compare against previous reports to generate a trend of growth you may be experiencing.

How it works

The query is very simple - it extracts the database metrics from the sys.database_files collection, and formats it in an easy-to-read style.

Create a temp table to store the results in, in this case named ##temp

Retrieve the raw metrics from sys.database_files, pass the database names to sp_msforeachdb and insert the results into the temp table - formatting each column appropriately (ie size and freespace into floating point values with two decimal places)