Friday, January 29, 2021

Calculate Sql Server database size having only db_reader permissions on target database

There are several possible ways to calculate database size (the same size which is shown when you right click on the database in Sql Server Management Studio > Properties > Files):

In this example we have 23 Mb of database file and 11 Mb of transaction log, total 34 Mb.

One way we can try is to run the following query:

select db_name(database_id) as database_name, 
    type_desc, 
    name, 
    size/128.0 as CurrentSizeMB
from sys.master_files
WHERE DB_NAME(database_id) = 'MyDatabase'

If you have permissions to run this command result will look like this:

As you can see it shows the same numbers as properties window shown above. The problem is that according to documentation you should have quite high server-level permissions to access sys.master_files:

The minimum permissions that are required to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.

If you don’t have one of these permissions result will be empty.

Is it possible to calculate database size having less permissions? E.g. having only db_reader permissions on target database. The answer is yes it is possible. In order to do that we need to use system stored procedure sp_spaceused:

use MyDatabaseName
exec sp_spaceused

It works also with db_reader permissions on the target database. Result will look like this:

It returns 2 result sets and in 1st result set it returns overall database size which is sum of db file name and transaction log (34 Mb in our example).

No comments:

Post a Comment