Two Basic Questions every DBA has to deal with routinely

1. How much Disk Space is my Database currently using on Disk and what is the real Data Volume in the Database files?

2. How close is the Data Volume to the Storage Limits?

The following article will show you how to extract all relevant information using T-SQL for automation - step by step.

If you simply need a quick and dirty solution without going through the details feel free to jump the code block labelled PUTTING IT ALL TOGETHER at the very end of this article.

SYSFILES

The first table we want to look at is the sysfiles table, which exists in every SQL Server Database. For the purpose of demonstration we execute following query on Microsofts Demo Database - Adventureworks:

AخA
 
1
select * from sysfiles

The result should look similar to this:

The [size] column defines the current file size for each file in pages, which is equivalent to 8kb.

The [maxsize] column represents maximum filesize set for the file, in pages, same pattern as [size], if the value is set - which is good practice. In case you received a negative value on [maxsize], this means the file size is unlimited. Don't worry about it - we will deal with this later.

Now let's improve the query to display the data we are interested in - to derive the sizes in MB we simply divide the counted pages by 128:

7
 
1
select
2
FileID,
3
rtrim(name)                                 FileLogicalName, 
4
rtrim(filename)                             FileFullPath,
5
cast([size]/128.0 AS DECIMAL(19,2))         FileSizeOnDiskMB,
6
cast([maxsize]/128.0 AS DECIMAL(19,2))      MaxFileSizeMB
7
from sysfiles

The result should look similar to this:

Not too bad for a start - we now know the file size and the capacity limits and can determine a ratio for monitoring.

But, we are not satisfied yet! What we don't know is the real size of the data population. Are the data in the database fully occupying the database files? In practice you are very likely to encounter files which contain many giga bytes filled with zeroes. This is very typical for log files and temp databases with autogrowth configuration, or staging databases used in ETL processes.

FILEPROPERTY 'SpaceUsed'

To determine the real space used within a database file we use the system funtion FILEPROPERTY - with two arguments: the logical filename and 'SpaceUsed' as string.  We therefore extend our code as follows - see line 5:

8
 
1
select
2
FileID,
3
rtrim(name)                                                     FileLogicalName, 
4
rtrim(filename)                                                 FileFullPath,
5
cast(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(19,2))    SpaceUsedWithinFileMB,  -- REAL DATA SIZE
6
cast([size]/128.0 AS DECIMAL(19,2))                             FileSizeOnDiskMB,
7
cast([maxsize]/128.0 AS DECIMAL(19,2))                          MaxFileSizeMB
8
from sysfiles

To demonstrate the empty file scenario I will run the query against the new SQL Server 2016 Demo Database WideWorldImporters - the query result should look similar to this:

NOTE: You must call the FILEPROPERTY functon from within the database - it will return NULL otherwise (!)

As you can see, the data occupy less than 20 percent of the disk space occupied by the database files - the rest is zeroes.

In practice, the empty reserved space might be intended and good practice to prevent fragmentation - if we expect large data volumes coming in future. In case of this demo database I would consider shrinking the database files WWI_Primary and WWI_UserData and thus release 2 GB for better use on my prescious SSD drive.

We know the file sizes, we know the real data sizes and the file size limits - Great! We are almost done!

There is a catch though. The [maxsize] information is insufficient for automation in operations. The maximum filesize may be set to 2 TB or even to unlimited (0,01) as in the example above - the sad truth however is that drive L: is a volume significantly smaller than [maxsize] and unlimited size harddrives have yet to be invented to support the settings above. Therefore, if  one of volumes D:  or L: run full on the FileSystem - our database will stop working, no matter what MaxFileSize indicates.

In practice, you might have your own tools to monitor disk space, or may have implemented your own Powershell or WMI queries for this purpose. In this case, you can compare the file system data with the SQL Meta Data above and configure events accordingly. If not, read on. The following section will show you how to extract and integrate the disk space information using t-sql.

SYS.DM_OS_VOLUME_STATS

To retrieve file system information in SQL Server we use the table function  sys.dm_os_volume_stats  - it takes two arguments: [Database_ID] and [File_ID] . The FileID is already known from our last query - the Database_ID for the current database you get by calling the scalar function DB_ID().

For a first glance - let's execute a query to see the output for the master.mdf in the Master Database (Database_ID = 1 and File_ID = 1 on all Systems):

4
 
1
use master
2
go
3
select database_id, file_id, volume_mount_point, file_system_type, total_bytes, available_bytes 
4
from sys.dm_os_volume_stats(DB_ID(), 1)

The result should look similar to this:

[available_bytes] is the free disk space on the volume D: where the file queried is located! Perfect!

Now all we need to do is compare the free space on disk with the maxim file size and use the limit which applies first for monitoring and event automation. Our last example shows demonstrates a working example.

PUTTING IT ALL TOGETHER

In the following example we merge the our work on sysfiles, fileproperty and volume statistics. We compare the space on volume with the maximum file size and create a derived column [CurrentExpansionLimitMB], which provides reliable information on the real file size limit to be considered for monitoring.

12
 
1
SELECT DB_ID() DatabaseID, FileID, DB_Name() DatabaseName, rtrim(name) FileLogicalName, filename FileFullPath,
2
cast([size]/128.0 AS DECIMAL(19,2))                             AS [FileSizeMB],        -- size, spaceused and maxsize are counted in Pages (8k) i.e PageCount / 128 = MB
3
cast(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(19,2))    AS [SpaceUsedMB],       -- SpaceUsed is the real space used within a DB File (rest zeros)
4
cast([maxsize]/128.0 AS DECIMAL(19,2))                          AS [MaxFileSizeMB],     -- maxsize indicates the file size growth limit
5
cast(available_bytes/SQUARE(1024) AS decimal(19,2))                 AS SpaceOnVolumeMB,     
6
CurrentExpansionLimitMB =   case --Determining Max Data Size (FileSize or Diskspace Limit)
7
                            when cast([maxsize]/128.0 AS DECIMAL(19,2)) < 0 then cast(available_bytes/SQUARE(1024) AS decimal(19,2)) -- if unlimited growth, then volume size is limit
8
                            when cast([maxsize]/128.0 AS DECIMAL(19,2)) > cast(available_bytes/SQUARE(1024) AS decimal(19,2)) then cast(available_bytes/SQUARE(1024) AS decimal(19,2)) -- special case: file size limit higher than disk space availabe (bad!) - in this case we use SpaceOnVolume as Limit
9
                            else cast([maxsize]/128.0 AS DECIMAL(19,2)) end
10
FROM        sysfiles as dbStorageInfo                                       -- DB File Information  
11
cross apply sys.dm_os_volume_stats(DB_ID(), dbStorageInfo.fileid)           -- Disk Space Information
12
ORDER BY FileID

Let's see how the output looks like for WideWorldImporters now:

As you can see [CurrentExpansionLimitMB] is derived from SpaceOnVolumeMB if MaxFileSizeMB exceeds drive limitations. 

We now have reliable information at hand on data volume within database files and on the file system to implement effective monitoring and maintenance automation for SQL Server Database files.

Feel free to use the code to implement your own views, procedures and improve your maintenance tasks or monitoring bots. Good luck!