Microsoft SQL Server logo

Easily querying all the database and log file sizes on SQL Server

Reading Time: 2 minutes.

Quite often you get the need to simply quickly query all the database sizes on MS SQL Server – most typically, for us, this need arises when a development machine gets low on disk space, and we need to check if some database’s log size has been growing, or if something else is wrong.

In my daily work, I rarely encounter this requirement anymore. Azure has almost entirely obliterated the need for such diagnostics, since it’s so easy to spin up or scale up new development resources when need be.

Solution

Table of Contents

The script below will query all database sizes and return them in a neat table. Nice and easy!

WITH fs
AS
(
    SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
    FROM sys.master_files
)
SELECT 
    name,
    (SELECT SUM(SIZE) FROM fs WHERE TYPE = 0 AND fs.database_id = db.database_id) DataFileSizeMB,
    (SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id) LogFileSizeMB
FROM sys.databases db

Postface

This post is kind of a classic – it was one of the first things I documented on my blog on 2014. But since it’s almost a one-liner, I felt silly about publishing it – and whenever I might’ve needed the SQL commands, I’ve just been accessing it from the admin interface ever since.

That’s cumbersome, though, so I finally thought it’s worthwhile to publish it after all.

Antti K. Koskela

Antti Koskela is a proud digital native nomadic millennial full stack developer (is that enough funny buzzwords? That's definitely enough funny buzzwords!), who works as a Solutions Architect for Valo Intranet, the product that will make you fall in love with your intranet. Working with the global partner network, he's responsible for the success of Valo deployments happening all around the world.

He's been a developer from 2004 (starting with PHP and Java), and he's been bending and twisting SharePoint into different shapes since MOSS. Nowadays he's not only working on SharePoint, but also on .NET projects, Azure, Office 365 and a lot of other stuff.

This is his personal professional (e.g. professional, but definitely personal) blog.
mm

2
Leave a Reply

avatar
5000
1 Comment threads
1 Thread replies
2 Followers
 
Most reacted comment
Hottest comment thread
2 Comment authors
Antti K. KoskelaFred Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
Fred
Guest
Fred

Hey Anti, Thanks for the script!

I’ve tried your script and all I get are NULL. Why is that ?