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

This post was most recently updated on February 5th, 2021.

2 min read.

Quite often you get the need to simply quickly query all the database sizes on MS SQL Server. Most typically, for me, this need arises when a development machine gets low on disk space, and I 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

Luckily, this is quite a simple task to accomplish, if you have high enough permissions on the SQL server. Essentially, you need to be able to query system tables – you’ll need VIEW SERVER STATE permissions, which you should be able to grant with this permission:

GRANT VIEW SERVER STATE TO contosouser;

Then you can run the script below, which will query all database sizes (both Data and Log files) on SQL Server 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 being one of the first things I documented on my blog in 2014. But since it’s almost a one-liner, I felt silly about publishing it… And whenever I might’ve needed the script again, I just accessed the draft version of the page.

That’s cumbersome, though, so I finally thought it’s worthwhile to publish it after all. Happy to see it’s been helpful for a lot of people!

Author
Recent Posts

Antti K. Koskela

Developer / Speaker / Consultant at Koskila / Precio Fishbone / Norppandalotti Software Co / Alter - Experience Ideas Ltd

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 Solutions Architect for Precio Fishbone, building delightful Digital Workplaces.

He's been a developer from 2004 (starting with PHP and Java), and he's been working on .NET projects, Azure, Office 365, SharePoint and a lot of other stuff. He's also Microsoft MVP for Azure.

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

Latest posts by Antti K. Koskela (see all)