This post was most recently updated on November 30th, 2020.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.
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 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
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.
- Pulumi task on Azure DevOps fails with error “azureblob.OpenBucket: accountName is required” - November 24, 2020
- Hacktoberfest 2020 - November 17, 2020
- How to access the site collection app catalog in SharePoint? - November 11, 2020