Having enough free disk space on your servers is critical for routine processes such as saving data, backups and other processes running on the SQL server. Consequently, it is important to know how to check whether you have enough free disk space on your server or whether more is needed.
The most common way to check whether there is enough free disk space in the server is with the SQL Server Management Studio which functions as follows:
The stored procedure sys.xp_fixeddrives (SQL Server 2005 and up) checks free disk space. With this information you will still need to know whether your free disk space is greater than the required minimum for any process. You can figure this out using the following stored procedure:
CREATE PROCEDURE dbo.spExec_SufficientDiskSpace @MinMBFree int, @Drive char(1) AS
/*
----------------------------------------------------------------------------
-- Object Name: dbo.spExec_SufficientDiskSpace
-- Dependent Objects: master.sys.xp_fixeddrives
-- Called By: Admin Scripts
--------------------------------------------------------------------------------------
*/
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
Using this method of checking, you would have to add to every process that requires disk space with an exec command that runs the stored procedure with the relevant parameter values. @MinMBFree would contain the minimal free disk space needed for the process and @Drive would contain the relevant drive letter of the drive used by the process. In case you would like to receive an email a notification when there is not enough free disk space, you’d have to embed the SQL code in HTML in order to enable emailing.
In contrast to this traditional, time-consuming approach of checking available disk space, the AimBetter system monitors the free disk space of all of the disks in all of the defined servers and alerts the AimBetter team about any decrease of free disk space below the threshold we’ve set, whether on the system level, the server level or on the specific disk level. With AimBetter you can also compare the trends of free disk space on a daily, weekly or monthly basis and therefore know whether the pattern is routine or exceptional so that you can respond accordingly.
The AimBetter system samples every predefined server every minute so that whenever you are logged into the system you can view the free disk space of any disk in any server monitored by it with no need to manually run any scripts whatsoever.
In addition to informing you know how much disk space is used by any monitored disk, AimBetter also tells you how busy your disk is percentagewise (in other words, how much disk space is currently used by READ/WRITE processes).
Lastly, the AimBetter system can also send email alerts according to your settings so that you can know about any exceptions to your thresholds immediately without the need to manually check your free disk space before any disk space-consuming process takes place. The system can also provide alerts about exceptional decreases in free disk space as indicated by your settings.
If free disk space is a critical factor or problem for your company, AimBetter can provide a convenient, comprehensive solution by monitoring your disk space, highlighting usage trends and alerting you about any decrease below your thresholds and/or exceptional decreases as indicated by your settings.