Setup a low disk space alert for SQL Server drives

Setup a low disk space alert for SQL Server drives

This is the pretty easy and clean script that you may use to check periodically your database server drive space.

You can create a job to run on hourly basis to execute this code, along with a database mail code to send you alert if any of drive has less than 15% or 20% threshold value.

-- Create a global temp table
CREATE TABLE ##space( dletter varchar(3), tspace BIGINT, fspace int, percentfree numeric(5,2))

-- Insert drive details
INSERT INTO ##space (dletter, fspace) EXEC master.dbo.xp_fixeddrives

-- Declare variables
DECLARE @oFSO INT, @oDrive INT, @drsize VARCHAR(255), @ret INT

-- invoke OACreate
EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT
DECLARE @dletter VARCHAR(3), @fspace INT, @tspace BIGINT
while (select count(*) from ##space where tspace is null)>0

begin
select top 1 @dletter = dletter + ':\',@fspace = fspace from ##space where tspace is null
EXEC @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter
EXEC @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT
UPDATE ##space SET tspace = CAST(@drsize AS BIGINT) WHERE lower(dletter) + ':\' = lower(@dletter)

EXEC master.dbo.sp_OADestroy @oDrive
end

EXEC master.dbo.sp_OADestroy @oFSO

update ##space set percentfree = fspace/((tspace/1024.0)/1024.0)*100

-- Select your data

select [Drive] = dletter ,
[Total Space GB]= convert(numeric(10,3), (tspace/1024.0)/1024.0/1024) ,
[Free Space GB]=convert(numeric(10,3),fspace/1024.0) ,
[% Free]= percentfree
from ##space

-- Drop temporary table

drop table ##space