Capturing Disk Space within SQL Server – 2000 to Current

Over the years, there have been many different ways to capture disk space within SQL Server.    Some of them were a lot more “user friendly” than others, but in the end we just want to get the job done.  Here are the ways that I am aware of through the years and some of the advantages and disadvantages of each.

SQL 2000-2005

In the SQL 2000 days, there were not a ton of options for the person who wanted to run stuff out of the SQL Agent and load it to SQL Server.   You really had just a couple of things:

  • xp_fixeddrives
/*Option 1 - the easy approach that does not give you quite as much information*/
CREATE TABLE FreeSpace
(
 id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
 ,DriveLetter VARCHAR(5)
 ,FreeSpaceMB INT
 ,DateCreated DATETIME DEFAULT GETDATE()
);
GO

And then you could run a process that would load this data into your tables periodically.

INSERT INTO dbo.FreeSpace(DriveLetter, FreeSpaceMB)
EXEC xp_fixeddrives;

This is not an awful option, it is easy to set up and runs quickly and requires no real “coding” knowledge outside of basic T-SQL, but it gives you a very limited amount of information.   You only see the drives that SQL is doing something with and you only see the Free Space that is on those drives.  In many cases that is good enough but it is certainly not fantastic.

  • xp_cmdshell or command prompt options

Doing this you can get the information in a variety of ways, so I will only go through a very simple one just to give you a feel.   It is nice, in that you can find out what the total space is and what the free space is but it is a bit of a pain because of the way that you have to roll through to get the drive letters available.   If you need to have Total Space along with Free Space and you are still running SQL 2000 (which I sincerely hope you are not) then this will get it for you.

First the set up:  Create our table to store this data.

/*Option 2 - More information but a big mess.*/
CREATE TABLE DriveSpace
(
 id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
 ,DriveLetter VARCHAR(5)
 ,FreeSpaceGB INT
 ,TotalSpaceGB INT
 ,DateCreated DATETIME DEFAULT GETDATE()
);
GO

Now we go through and get all of this information.  This is a many stepped process, so let me walk you through it before putting in the fairly lengthy script.    The first thing it has to do is grab the drives that are on the server.   This is pre-powershell (note I am assuming a SQL 2000 machine) so we had to do this with wmi calls through xp_cmdshell, so standard security rules and permissions apply.  After we have grabbed those disks, we then need to go and get the space data (free and total) for them.  That is also done through xp_cmdshell and specifically via fsutil.  Once we have grabbed that information, we have to parse through it and dump it to find the relevant values so that we can finally insert them into our DriveSpace table.  Long explanation, so now here is the code and I tried to comment it in a way that would separate those parts out as well as possible.

/*Create the table to store the local drives on the server*/
IF OBJECT_ID('tempdb..#drives') IS NOT NULL 
 DROP TABLE #drives;
GO
CREATE TABLE #drives
(
 DriveName VARCHAR(200)
);
GO
/*Get the list of local drives*/
INSERT INTO #drives
EXEC xp_cmdshell 'wmic logicaldisk get description,name | findstr /C:"Local"';
GO
/*Create the table to store the holding data of the drive information. Not well formed.*/
IF OBJECT_ID('tempdb..#diskspace_hold') IS NOT NULL
 DROP TABLE #diskspace_hold;
GO
CREATE TABLE #diskspace_hold
( ColumnOutput VARCHAR(200)
);
GO
/*Same as above, but with drive associated to it.*/
IF OBJECT_ID('tempdb..#diskspace') IS NOT NULL
 DROP TABLE #diskspace;
GO
CREATE TABLE #diskspace
( Drive VARCHAR(5)
 ,ColumnOutput VARCHAR(200)
);

GO
DECLARE @drive VARCHAR(2)
 ,@cmd VARCHAR(2000);
/*First get a distinct list of the drives*/
DECLARE csr CURSOR FOR
SELECT DISTINCT LEFT(LTRIM(REPLACE(DriveName, 'Local Fixed Disk', '')), 2) AS DriveLetter
FROM #drives
WHERE DriveName LIKE 'Local Fixed Disk%'

OPEN csr
FETCH NEXT FROM csr INTO @drive

WHILE @@FETCH_STATUS = 0
BEGIN
/*For each drive grab the disk space (free and total) to load into our table for parsing*/
SET @cmd = 'fsutil volume diskfree ' + @drive
PRINT @cmd
TRUNCATE TABLE #diskspace_hold
INSERT INTO #diskspace_hold
 ( ColumnOutput )
EXEC xp_cmdshell @cmd;

/*Load into our table with the drive associated to it*/
INSERT INTO #diskspace
 ( Drive, ColumnOutput )
SELECT @drive, 
 ColumnOutput 
FROM #diskspace_hold AS dh

FETCH NEXT FROM csr INTO @drive;
END
CLOSE csr;
DEALLOCATE csr;
GO
DECLARE @drive VARCHAR(5),
 @gbfree int,
 @gbtotal int;
/*Now that we have our drives with disk space data, parse it out to a usable integer style format*/
DECLARE csr CURSOR
FOR
 SELECT DISTINCT
 d.Drive
 FROM #diskspace AS d;

OPEN csr;
FETCH NEXT FROM csr INTO @drive;

WHILE @@FETCH_STATUS = 0
BEGIN

 SELECT @gbfree = CAST(REPLACE(d.ColumnOutput, 'Total # of free bytes : ', '') AS BIGINT) / 1073741824
 FROM #diskspace AS d
 WHERE d.ColumnOutput LIKE 'Total # of free bytes%'
 AND d.Drive = @drive 

 SELECT @gbtotal = CAST(REPLACE(d.ColumnOutput, 'Total # of bytes : ', '') AS BIGINT) / 1073741824 
 FROM #diskspace AS d
 WHERE d.ColumnOutput LIKE 'Total # of bytes%'
 AND d.Drive = @drive 
 
 INSERT INTO dbo.DriveSpace
 (
 DriveLetter,
 FreeSpaceGB,
 TotalSpaceGB,
 DateCreated
 )
 SELECT 
 @drive, 
 @gbfree,
 @gbtotal,
 GETDATE();



 FETCH NEXT FROM csr INTO @drive;
END

CLOSE csr;
DEALLOCATE csr;
GO

 

SQL 2008

To begin with, the methods mentioned above will work for SQL 2008 as well.  But with SQL 2008 came the addition of Powershell as a SQL Agent step option.  This opened things up a little bit for those wanting to get disk space in a much cleaner way than what I have above.   That said, a fair number of people have put together various scripts to do this and the one that I prefer is the one that the Scripting Guy blogs about.  And even better than that, it goes through a whole lot more than just capturing disk space, it shows you how to capture data, in general, with Powershell.   It is an excellent read, and he does it a lot better than I could (especially since it is the method that I used with SQL 2008 anyway).  Plus it can cross servers, if you want a central repository.

 

SQL 2008 R2 and Higher

Once we got to SQL 2008 R2, everything got even simpler for those who just wanted to grab this data and store it locally.   I first read about this at SQLPerformance.com from a post by Erin Stellato talking about Proactive monitoring.  It just does not get easier than this.

SELECT DISTINCT
 vs.volume_mount_point AS [Drive],
 vs.logical_volume_name AS [Drive Name],
 vs.total_bytes/1024/1024 AS [Drive Size MB],
 vs.available_bytes/1024/1024 AS [Drive Free Space MB]
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
ORDER BY vs.volume_mount_point;

So that is my list of ways to capture drive information from within SQL Server from SQL 2000 to present.   It has morphed fairly drastically over the years and has gotten much easier over time.

 

Leave a Reply

Your email address will not be published. Required fields are marked *