I get the script form http://www.sqlmag.com/blog/practical-sql-server-45/sql-server-2012/disk-space-monitoring-143552
to monitor the available disk space of the disk SQL server owns:
it create SP using this:
CREATE PROC dbo.sp_drivespace_alerts
@threshold int -- number of MB under which to launch an alert
SET NOCOUNT ON
DECLARE @list nvarchar(2000) = '';
WITH core AS (
CAST(s.available_bytes / 1048576 as decimal(12,2)) [AvailableMBs]
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
SELECT @list = @list + ' ' + Drive + ', '
WHERE AvailableMBs < @threshold
IF LEN(@list) > 3 BEGIN
DECLARE @msg varchar(500) = 'Low Disk Space Notification. The following drives are currently reporting less than '
+ CAST(@threshold as varchar(12)) + ' MB free: ' + @list
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'xxxxx',
@recipients = @to,
@subject = @subject,
@body = @msg
1) I don't quite understand what is the WITH XXX As is about even from this article: http://msdn.microsoft.com/en-au/library/ms175972(v=sql.105).aspx
2) I dont' see it return the result of @threshold, from where @threshold has a value ? I don't see it has a value before it goes to "WHERE AvailableMBs < @threshold"
3) "IF LEN(@list) > 3 BEGIN" , what is the LEN for ? I read http://msdn.microsoft.com/en-au/library/ms190329.aspx
and it seems LEN works like parser, to return number of character returned?
I don't see how this script works.