0

I need to receive an email when free drive space on E: or H: becomes less than 20%

I know that can be achieved by setting up a job that executes PowerShell or T-SQL scripts on a schedule, that checks for free drive space and sends an alert email when drive space is low

But the question is - can same task be achieved using just an Alert ?
There is alert type SQL Server performance condition alert

Is there an Object / Counter that can help to check for drive space and fire this alert if its value "Falls Below" specified threshold ?

Alert

1
  • 1
    Agent perf alerts can only work with the SQL perfmon counters. In the end, what is used is a polling from sys.dm_os_perfomance_counters DMV. These do not give you OS type info. So, some other mechanism would be needed here. Nov 11, 2020 at 15:10

1 Answer 1

1

This is an OS information. I don't think there will be an Alert in SQL that will track the remaining space on the drives.

Pretty much all of the monitoring products monitor this and can send you alerts when you reach a specific threashold for disk space.

If you do not have any monitoring tool, then I think that a SQL job that check the free space and send an email seems like a good option.

Here's a script I use to see how much space available there is on the drive where I have a database file (it doesn't work for drive that doesn't host any database file)

SELECT Drive,PercentFree FROM (SELECT  VS.volume_mount_point AS Drive, 
 CONVERT(DEC(4,2),CONVERT(DEC(18,0),VS.available_bytes)*100.00/VS.total_bytes) AS PercentFree 
,ROW_NUMBER() OVER(PARTITION BY VS.volume_mount_point ORDER BY MF.database_id,MF.[file_id]) RN
FROM sys.master_files MF CROSS APPLY sys.dm_os_volume_stats(MF.database_id, MF.[file_id]) VS) as SP WHERE RN=1 ORDER BY SP.Drive

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.