SQL Tips #4 - Listing all SQL databases with their file locations and sizes


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.

  1. Create a temp table to store the results in, in this case named ##temp
  2. 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)
  3. Return the temp table formated as we desire
  4. Drop the temp table

The Query


CREATE TABLE ##temp
(
    DatabaseName sysname,
    Name sysname,
    physical_name nvarchar(500),
    size decimal (18,2),
    FreeSpace decimal (18,2)
)

EXEC sp_msforeachdb '
USE [?];
INSERT INTO ##temp (DatabaseName, Name, physical_name, Size, FreeSpace)
    SELECT DB_NAME() AS [DatabaseName], Name,  physical_name,
    CAST(CAST(Round(CAST(size AS decimal) * 8.0/1024.0,2) AS decimal(18,2)) AS nvarchar) Size,
    CAST(CAST(Round(CAST(size AS decimal) * 8.0/1024.0,2) AS decimal(18,2)) -
    CAST(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 AS decimal(18,2)) AS nvarchar) AS FreeSpace
    FROM sys.database_files
'
SELECT DatabaseName,Name,size,FreeSpace,physical_name FROM ##temp
DROP TABLE ##temp

The Output

The query will return results similar to those below. I've replaced my own table names with 'tablename' for privacy reasons.

DatabaseName    Name        Size        FreeSpace   physical_name
master	        master	    5.38	1.38	    ...DATA\master.mdf
master	        mastlog	    2.00	1.30	    ...DATA\mastlog.ldf
model	        modeldev    8.00	5.31	    ...DATA\model.mdf
model	        modellog    8.00	6.94	    ...DATA\modellog.ldf
msdb	        MSDBData    17.94	0.13	    ...DATA\MSDBData.mdf
msdb	        MSDBLog	    19.63	17.63	    ...DATA\MSDBLog.ldf

Disclaimer

These queries are provided as a guide, and are by no means perfect. I use these on Microsoft SQL Server 2012, 2014 and 2016.

Tags

SQL, Programming
Useful SQL tips - How to list all your SQL databases with their file locations and sizes
 
 

Popular Articles

What is Kutamo?
Kilimanjaro 2015
Exploring Lync and IoT
Exchange 2013 in 60 minutes
Monitoring Lync with MRTG
Lync UCWA Tutorial - Introduction
Tutorial Parts 1 | 2 | 3 | 4 | 5

Recent Articles

Australian Postal Codes
Skype Web SDK
Using the Skype Web SDK from any language or framework
Building a Skype for Business Auto Responder using the Skype Web SDK
Exporting Lync or Skype for Business Contacts with the Skype Web SDK

Favourite Links

Kutamo
Telco Together Foundation
Cloud on Kilimanjaro

Tags / Keywords

SQL, Programming