Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Determine Free Disk Space in SQL Server with TSQL Code


By:   |   Read Comments (5)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Database Administration

Problem

At our organization we need to check for a minimum amount of free space before proceeding with some processes that run on SQL Server 2000, 2005 and 2008 SQL Server instances. Do you know of a way to find out the free disk space and then fail the process if it does not meet the minimum requirements?  Can you provide some sample code?

Solution

Checking for free disk space before proceeding with a process is a wise move if disk space is tight or a high percentage of the drive is needed for the process.  It is disconcerting to have a process run for hours only to fail towards the end of the process due to insufficient disk space.  Although a few different options are available to check for disk space (CLR, WMI, PowerShell, etc.) in SQL Server, let's see how we can use the xp_fixeddrives extended stored procedure which is available in SQL Server 2000 to 2008.


Sample Stored Procedure to Assess the Free Disk Space on a SQL Server Disk Drive

In the sample stored procedure below, it is accepting a parameter for the minimum amount of megabytes (MB) free on a specific disk drive, then executing the master.sys.xp_fixeddrives extended stored procedure into a temporary table.  Once the data is in the temporary table the current amount of free disk space is compared to the minimum amount of free disk space to determine if the process should continue or raise an error.

One item to keep in mind is that between SQL Server 2000 and SQL Server 2005/2008 the owner for the xp_fixeddrives extended stored procedure changed.  In SQL Server 2000, xp_fixeddrives was owned by dbo and in SQL Server 2005/2008 the owner is sys.  Due to this ownership change, two stored procedures are provided below.  One for SQL Server 2005/2008 and a second for SQL Server 2000.

SQL Server 2008 and 2005 Version

CREATE PROCEDURE dbo.spExec_SufficientDiskSpace @MinMBFree int, @Drive char(1) AS

/*
----------------------------------------------------------------------------
-- Object Name: dbo.spExec_SufficientDiskSpace
-- Project: Admin Scripts
-- Business Process: Monthly Sales Reports
-- Purpose: Validate sufficient disk space
-- Detailed Description: Validate sufficient disk space based on based on the 
-- @MBfree and @Drive parameters 
-- Database: Admin
-- Dependent Objects: master.sys.xp_fixeddrives
-- Called By: Admin Scripts
-- Upstream Systems: Unknown
-- Downstream Systems: Unknown
-- 
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
--------------------------------------------------------------------------------------
-- 001 | N\A | 03.05.2009 | MSSQLTips | Original code
--
*/

SET NOCOUNT ON

-- 1 - Declare variables
DECLARE @MBfree int

-- 2 - Initialize variables
SET @MBfree = 0

-- 3 - Create temp tables
CREATE TABLE #tbl_xp_fixeddrives
(Drive varchar(2) NOT NULL,
[MB free] int NOT NULL)

-- 4 - Populate #tbl_xp_fixeddrives
INSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])
EXEC master.sys.xp_fixeddrives

-- 5 - Initialize the @MBfree value
SELECT @MBfree = [MB free]
FROM #tbl_xp_fixeddrives 
WHERE Drive = @Drive

-- 6 - Determine if sufficient free space is available
IF @MBfree > @MinMBFree
 BEGIN
  RETURN
 END
ELSE
 BEGIN
  RAISERROR ('*** ERROR *** - Insufficient disk space.', 16, 1)
 END

-- 7 - DROP TABLE #tbl_xp_fixeddrives
DROP TABLE #tbl_xp_fixeddrives

SET NOCOUNT OFF
GO

 

SQL Server 2000 Version

CREATE PROCEDURE dbo.spExec_SufficientDiskSpace @MinMBFree int, @Drive char(1) AS

/*
----------------------------------------------------------------------------
-- Object Name: dbo.spExec_SufficientDiskSpace
-- Project: Admin Scripts
-- Business Process: Monthly Sales Reports
-- Purpose: Validate sufficient disk space
-- Detailed Description: Validate sufficient disk space based on based on the 
-- @MBfree and @Drive parameters 
-- Database: Admin
-- Dependent Objects: master.sys.xp_fixeddrives
-- Called By: Admin Scripts
-- Upstream Systems: Unknown
-- Downstream Systems: Unknown
-- 
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
--------------------------------------------------------------------------------------
-- 001 | N\A | 03.05.2009 | MSSQLTips | Original code
--
*/

SET NOCOUNT ON

-- 1 - Declare variables
DECLARE @MBfree int

-- 2 - Initialize variables
SET @MBfree = 0

-- 3 - Create temp tables
CREATE TABLE #tbl_xp_fixeddrives
(Drive varchar(2) NOT NULL,
[MB free] int NOT NULL)

-- 4 - Populate #tbl_xp_fixeddrives
INSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])
EXEC master.dbo.xp_fixeddrives

-- 5 - Initialize the @MBfree value
SELECT @MBfree = [MB free]
FROM #tbl_xp_fixeddrives 
WHERE Drive = @Drive

-- 6 - Determine if sufficient free space is available
IF @MBfree > @MinMBFree
 BEGIN
  RETURN
 END
ELSE
 BEGIN
  RAISERROR ('*** ERROR *** - Insufficient disk space.', 16, 1)
 END

-- 7 - DROP TABLE #tbl_xp_fixeddrives
DROP TABLE #tbl_xp_fixeddrives

SET NOCOUNT OFF
GO

 

Next Steps


Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, March 26, 2013 - 4:24:33 PM - Douglas Brown Back To Top

Thank you Jeremy.


Friday, March 22, 2013 - 8:56:30 PM - Jeremy Kadlec Back To Top

Doug,

That variable is unnecessary in this logic.  I must have updated the logic and not removed the variable.  I have updated the logic.

Thank you,
Jeremy Kadlec
Community Co-Leader


Wednesday, March 20, 2013 - 11:45:02 PM - Doug Brown Back To Top

 

Hi Jeremy

 

 

I was looking at your tip free disk space monitoring and had a few questions about the variables. I understand the 'SET @MBfree =' is the threshold I set to compare to the free space data inserted to the temp table but not sure about 'SET @CMD1 ='. Is this the drive parameter and if so is there a way to set it for all disks?


Wednesday, February 22, 2012 - 3:31:28 PM - victormlima Back To Top

 

HINT:

IF @Drive = '',  Use this code to search only in used Data folder

 

SELECT @MBfree = [MB free]

FROM #tbl_xp_fixeddrives 

WHERE ([MB free] < @MinMBFree or @MinMBFree = 0) and 

((Drive = @Drive) OR 

(@Drive = '' AND Drive IN (SELECT distinct upper(SUBSTRING(physical_name, 1,1)) AS current_file_location

FROM sys.master_files)

))

 


Thursday, March 19, 2009 - 6:12:10 AM - nstaudt Back To Top

 Rather than having a separate code listing for 2000 vs 2005, you can just refer to that proc as  master..xp_fixeddrives

 This works for both versions :-)


Learn more about SQL Server tools