Get Your SQL Server Database Size with Ease: Stored Procedures in Action

As popular as the cloud services are nowadays, there’s still a good chunk of on-premises deployments of SQL Server that still require our services to support them. One of the areas of on-premises setups that we must keep an eye on is storage, right where the data is saved.

I’m going to present you a Stored Procedure to visualize key storage space information within your SQL Server instance.

What to Keep in Mind When Managing Disk Space in SQL Server

• Make sure that the account executing this Stored Procedure has enough privileges.
• The database objects (database table and stored procedure) will be created inside the database selected at the time that the script is executed, so choose carefully.
• The script is crafted in a way that it can be executed multiple times without getting an error thrown at you. For the Stored Procedure, I used the CREATE OR ALTER PROCEDURE statement, available since SQL Server 2016 SP1.
• Feel free to change the name of the created database objects.
• When you choose to persist data returned by the Stored Procedure, the target table will be first truncated so only the most recent result set will be stored.
• Keep in mind that this solution doesn’t make sense in cloud deployments where the cloud provider manages things for you and you don’t have access to the file system.

How to Use Stored Procedure?

1. Copy & paste the TSQL Code (available within this article).
2. The SP expects 2 parameters:
   1. @persistData: ‘Y’ if the DBA desires to save the output in a target table, and ‘N’ if the DBA only wants to see the output directly.
   2. @driveDetail: Although optional, if you pass a drive letter, then the @persistData parameter will have no effect whatsoever.

Fields in Focus: Their Role in SQL Server Space Availability

• drive: the drive letter that contains data files for the current instance.
• total_space: the size of the drive, in GBs.
• free_space: the amount of GBs left in the drive.
• used_space: the amount of GBs occupied by all the databases in the instance.
• data_collection_timestamp: visible only if ‘Y’ is passed to the @persistData parameter, and it is used to know when the SP was executed and the information was successfully saved in the DBA_Storage table.

Using Stored Procedures to Check Disk Space in SQL Server

I will demonstrate a few executions of the Stored Procedure so that you can get an idea of what to expect from it:

EXEC GetStorageData @persistData = 'N'

Since I ran this in a test instance where I have everything stuffed in C:\ drive (I know, the worst practice ever), only one row was returned. Now, let me show you a screenshot of the usage of my C:\ drive, as reported by Windows, just to see if the SP isn’t bluffing around:

It looks good, for the most part. However, if you take a closer look, you’ll notice that the “Used space” in the graphic says 25GB and the SP says “0.170GB”, that’s odd right? Well, the reason is that the meaning in the SP is a bit different: here it reports the amount of GBs occupied only by database files, so keep that in mind.

Now, that output seems a bit dry, doesn’t it? I mean, we don’t know what exactly is taking the reported used space. That’s where the other parameter comes into play, so let’s check it out:

EXEC GetStorageData @persistData = 'N', @driveDetail = 'C'

Executing it like this will give you the list of specific databases that have at least 1 database file in the drive passed as a parameter. If you sum the “total space” column, it will give you the exact same value as the previous summarized output.

Let me try one more thing to see what the SP returns. I’m going to create a new database, but I’ll place the database files in another drive that I have laying around. I’m calling the database “test” and I will place it in drive S:\.

So now the SP also outputs that drive in the result set. But again, let’s see what happens if we throw the @driveDetail parameter with ‘S’ as a value:

Bingo, it reports the ‘test’ database I created with the size I picked (1GB for the data file and 8MB for the transaction log file).

Crafting the Perfect Stored Procedure: Visualizing Used Disk Space

Now, to deliver more value to the DBA, I have prepared a few queries that can help you to obtain useful information from the data persisted in the table.

*Query to find databases with at least 1 data file hosted in C:\ drive.

SELECT * FROM DBA_Storage WHERE drive = 'C:\';

*Query to visualize the list of drives sorted by free_space, from lowest to highest. With this, you can know which drives need your attention as soon as possible.

SELECT * FROM DBA_Storage ORDER BY free_space;

*Query to visualize the list of drives sorted by used_space, from highest to lowest. With this, you can know which ones have more data than the others.

SELECT * FROM DBA_Storage ORDER BY used_space DESC;

Here’s the complete code of the Stored Procedure:

*At the very beginning of the script, you will see the default value that the Stored Procedure assumes if no value is passed for each parameter.

CREATE OR ALTER PROCEDURE [dbo].[GetStorageData] 
	@persistData   CHAR(1) = 'Y',
	@driveDetail   CHAR(1) = NULL
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @command NVARCHAR(MAX)    
	
	DECLARE @Tmp_StorageInformation TABLE(       
	[drive]                     [CHAR](3) NOT NULL,
	[total_space]               [DECIMAL](10,3) NOT NULL,
	[free_space]                [DECIMAL](10,3) NOT NULL,
	[used_space]                [DECIMAL](10,3) NOT NULL
	)
	
	IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'DBA_Storage') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		CREATE TABLE DBA_Storage(
		[drive]                     [CHAR](3) NOT NULL,
		[total_space]               [DECIMAL](10,3) NOT NULL,
		[free_space]                [DECIMAL](10,3) NOT NULL,
		[used_space]                [DECIMAL](10,3) NOT NULL,
		[data_collection_timestamp] [DATETIME] NOT NULL
		)
	END

	
	IF(@driveDetail IS NOT NULL)
	BEGIN
		SELECT DB_NAME(mf.database_id) AS 'database',CONVERT(DECIMAL(10,3),SUM(size*8)/1024.0/1024.0) AS 'total space'
		FROM sys.master_files mf
		WHERE SUBSTRING(mf.physical_name,0,4) = CONCAT(@driveDetail,':\')
		GROUP BY mf.database_id
		
		RETURN 
	END
	
	INSERT INTO @Tmp_StorageInformation   
	SELECT 
		   drives.drive,
		   drives.total_space,
		   drives.free_space,
		   (SELECT CONVERT(DECIMAL(10,3),SUM(size*8)/1024.0/1024) FROM sys.master_files WHERE SUBSTRING(physical_name,0,4) = drives.drive) AS 'used_space'
	FROM(
		 SELECT DISTINCT vs.volume_mount_point AS 'drive',CONVERT(DECIMAL(10,3),(vs.available_bytes/1048576)/1024.0) AS 'free_space',CONVERT(DECIMAL(10,3),(vs.total_bytes/1048576)/1024.0) AS 'total_space'
		 FROM sys.master_files mf
		 CROSS APPLY sys.dm_os_volume_stats(mf.database_id,mf.file_id) vs
		) AS drives      
	  
	IF @persistData = 'N'
		SELECT * FROM @Tmp_StorageInformation 
	ELSE 
	BEGIN
		TRUNCATE TABLE DBA_Storage
		
		INSERT INTO DBA_Storage
		SELECT *,GETDATE() FROM @Tmp_StorageInformation ORDER BY [drive] 
	END
END

Conclusion

• You can deploy this SP in every SQL Server instance under your support and implement an alerting mechanism across your entire stack of supported instances.
• If you implement an agent job that is querying this information relatively frequently, you can stay on top of the game in terms of taking the steps to take care of the storage whenever certain thresholds are met, within your supported environment(s).
• Make sure to check out more tools that are published here at CodingSight.