How to track SQL Server database space usage with built-in functions and DMVs

Introduction

In the previous article in this series on dynamic management views in SQL Server, we used DMV’s to discover some basic information about databases on an instance you are connected to, along with some ideas about getting basic file system and security info. I promised to dive into security next but first I’d like to explore a topic that is currently quite critical for the company I work for today: tracking space usage.

When will my database fill up the file system?

Currently, I look after a relatively new and quickly-growing database. We’ve partitioned the tables by month which keeps the individual partition size reasonable. However, there is only a finite amount of disk space available. I also know that the bureaucracy involved in asking for and getting additional disk space is daunting and time consuming. To be proactive, I keep track of how much space is used day by day. This then gives me the ability to project the growth of my database.

If I want to know when my file system will fill up, I need to know my baseline – what the files system space usage looks like today. I can do that with a simple query:

SELECT DISTINCT

SERVERPROPERTY('MachineName') AS MachineName

, ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLSERVER') AS InstanceName

, vs.volume_mount_point AS VolumeName

, vs.logical_volume_name AS VolumeLabel

, vs.total_bytes AS VolumeCapacity

, vs.available_bytes AS VolumeFreeSpace

, CAST(vs.available_bytes * 100.0 / vs.total_bytes AS DECIMAL(5, 2)) AS PercentageFreeSpace

FROM sys.master_files AS mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs;

RETURN;

Oy my laptop, the display isn’t too exciting:

On one of the production systems I maintain, it’s more interesting. Try it on any system you can connect to and where you have the right permissions. Microsoft documentation says that you need one of CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.

By the way, this query uses the instance-level view:

sys.master_files

which is a superset of:

sys.database_files

that I discussed in the last article. It also uses the Dynamic Management Function:

sys.dm_os_volume_stats

How much headroom do I have?

Now, what I need to determine when I will run out of headroom on the file system is the current database file size and growth rate, the type of database autogrowth set, if any and the current file system space available. Using those properties, I can then calculate the approximate date of the last possible autogrow operation and the date when the file system will be completely full. I can take those projections to our SAN team to tell then how much space we will need and when it absolutely must be online, with a generous fudge factor to cover accelerated growth or delayed delivery of additional resources.

There are two interesting side-effects of the autogrow setting:

Even if my database autogrows to completely fill the file system, there may still be significant room inside the database. For example, if my database is 2 terabytes in size and grows at 2GB per day and it autogrows by 100GB, which then fills the file system, I still have 50 days left to expand the available space before things come to a dead stop.
If the autogrow increment is set to slightly less that the remaining space on the filesystem, things will come to a dead stop, since the database is full and cannot grow, unless I change the increment.

I hope to avoid both situations through compression and the timely archiving of data that no longer needs to be online.

What’s the autogrow setting for my database?

To determine the autogrowth setting, along with some other vital information, you can use this query:

DECLARE @mult float = 8192. / 1024 / 1024 / 1024;

SELECT df.physical_name AS FileName,

MAX(CAST(df.size AS FLOAT) * 8192 / 1024 / 1024 / 1024) AS Size,

CASE df.max_size

WHEN 0 THEN 'No growth is allowed.'

WHEN-1 THEN 'File will grow until the disk is full'

WHEN 268435456 THEN 'Log file will grow to a maximum size of 2 TB.'

ELSE 'Other'

END AS MaxSizeType,

df.max_size AS MaxSize,

CASE df.growth

WHEN 0

THEN 'File is fixed size and will not grow.'

ELSE 'File will grow automatically.'

END AS GrowthType,

CASE df.is_percent_growth

WHEN 0 THEN 'Percentage growth size'

WHEN 1 THEN 'Absolute growth size'

END AS GrowthIncrementType,

MAX(CAST(df.growth AS FLOAT) * @mult) AS GrowthGB

FROM sys.database_files df

GROUP BY df.physical_name,

df.max_size,

df.growth,

df.is_percent_growth

ORDER BY df.physical_name

Note that most of these system views return space in units of pages. To convert that to GB, I use the simple formula:

space_GB=pages * 8192/1024/1024/1024

In the query above, I use a variable to hold the multiplier. This makes the code easier to read and DRYer. If I want to change to MB or TB, I can just add or remove a divisor.

You could also do it inline, without using a variable, if you add a CROSS APPLY:

CROSS APPLY (SELECT CAST(8192. / 1024 / 1024 / 1024 AS float)) AS _(mult)

And replacing instance of “@mult” with just “mult.” There’s more than one way to do it!

The above query returns information that is easy to find in SSMS in database properties. The general page exposes the current size and space available:

The “Files” section shows the initial size and autogrowth parameters:

You can often use the GUI but I prefer to get as much using T-SQL as possible. It just gives you more options for processing the result.

Tracking space usage to determine growth rate

Now I only to know my growth rate. To do that, I’ll set up a little tracking table:

CREATE TABLE SpaceUsed

(SpaceDate datetime DEFAULT GETDATE(),

database_name NVARCHAR(128),

database_size VARCHAR(18),

unallocated_space VARCHAR(18),

reserved VARCHAR(18),

data VARCHAR(18),

index_size VARCHAR(18),

unused VARCHAR(18)

);

Then, I’ll populate it every day with this query, using the system stored procedure sp_spaceused. (I’m using the most current version of this proc. The @oneresult parameter is not available in all versions, service packs and CUs. You can achieve the same effect without this parameter, though. It’s just a little more work.)

INSERT INTO SpaceUsed

(database_name,

database_size,

unallocated_space,

reserved,

data,

index_size,

unused

)

EXEC sp_spaceused @oneresultset = 1;

This handy proc gives me all I need to finish my projection, once I populate it with data. (I’m using the most current version of this proc. The @oneresult parameter is not available in all versions, service packs and Cus. You can achieve the same effect without this parameter, though.) That’s easy. A daily SQL Server Agent job will do it. Once I’ve collected data for a few typical days, I can compute the growth rate, which is just the average day-over day difference. Now, the values are returned as text, which is an integer followed by a unit. To use the values, I need to convert them back to integers. (This is in contrast to other system views that return results in number of pages.) The unit could be KB, MB, GB, etc. I’ll show a technique to convert them to big integers according to the unit.

WITH s AS (

SELECT s.*, dunits/ddiv AS DataSpaceGB, iunits/idiv AS IndexSpaceGB

FROM @SpaceUsed s

CROSS APPLY

(

SELECT left(data, charindex(' ', data)), CASE RIGHT(RTRIM(data),2)

WHEN 'KB' THEN CAST(1024 * 1024 AS FLOAT)

WHEN 'MB' THEN CAST(1024 AS FLOAT)

WHEN 'GB' THEN CAST(1 AS FLOAT)

END

) #1(dunits, ddiv)

CROSS APPLY

(

SELECT left(data, charindex(' ', data)), CASE RIGHT(RTRIM(data),2)

WHEN 'KB' THEN CAST(1024 * 1024 AS FLOAT)

WHEN 'MB' THEN CAST(1024 AS FLOAT)

WHEN 'GB' THEN CAST(1 AS FLOAT)

END

) #2(iunits, idiv)

)

SELECT SpaceDate,

DataSpaceGB - LAG(DataSpaceGB, 1, 0) OVER(ORDER BY SpaceDate)

AS DailyDataGrowth,

IndexSpaceGB - LAG(IndexSpaceGB, 1, 0) OVER(ORDER BY SpaceDate)

AS DailyIndexGrowth

FROM s

This may look rather complicated, but it’s actually not that bad. Starting from the inside. The two CROSS APPLYS return the value and unit of the data and index space values. The units are returned as divisors to convert the values to GB. Then the converted values are used to compute the day-over-day difference, which are the growth factors I need.

The day the database stands still

Once I’ve collected all the information, the final projections are easy enough. The space available divided by the daily growth rate will tell me how many days until the database next tries to autogrow:

days_AG = free space / daily growth

The free space on the file system divided by the autogrow amount tells me how many more times the database can grow automatically:

n_AG = file system free space / autogrow amount

The number of days headroom I have is then:

days_headroom = days_AG * (n_AG + 1) – 1

Since the database can continues growing until the last expansion is completely filled. Now I need to make sure that that SAN order is moving along to meet that time!

Summary

A vital part of database management is knowing how fast your database is growing and when you’ll need to bring more capacity on line. This article showed some DMVs that can help, along with a little math!

Previous articles in this series:

Gerald Britton

Gerald Britton is a Senior SQL Server Solution Designer, Author, Software Developer, Teacher and a Microsoft Data Platform MVP. He has many years of experience in the IT industry in various roles.

Gerald specializes in solving SQL Server query performance problems especially as they relate to Business Intelligence solutions. He is also a co-author of the eBook "Getting Started With Python" and an avid Python developer, Teacher, and Pluralsight author.

You can find him on LinkedIn, on Twitter at twitter.com/GeraldBritton or @GeraldBritton, and on Pluralsight

View all posts by Gerald Britton

Latest posts by Gerald Britton (see all)

Snapshot Isolation in SQL Server - August 5, 2019
Shrinking your database using DBCC SHRINKFILE - August 16, 2018
Partial stored procedures in SQL Server - June 8, 2018