troubleshooting Question

WITH XXX AS statement

Avatar of marrowyung
marrowyung asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008
31 Comments3 Solutions1362 ViewsLast Modified:
Dear all,

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
	@from varchar(100),
	@to varchar(200),
	@subject varchar(100),
	@threshold int  -- number of MB under which to launch an alert
AS

	SET NOCOUNT ON

	DECLARE @list nvarchar(2000) = '';

	WITH core AS ( 
		SELECT DISTINCT
			s.volume_mount_point [Drive],
			CAST(s.available_bytes / 1048576 as decimal(12,2)) [AvailableMBs]
		FROM 
			sys.master_files f
			CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
	)

	SELECT @list = @list + ' ' + Drive + ', '
	FROM core
	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
		
	END

	RETURN 0
GO

question:

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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 3 Answers and 31 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 31 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros