asked on

Converting MB to GB.

Dear all,

right now I am trying to change the script below to display GB instead of MB.

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

Open in new window

Anyway to make it in such a way that if the amount of space left is < 1GB, we leave this script as it is .

If it is > 1GB, then the amount of free space should be report in GB.

Harish Varghese

In which part of the script, do you want MB Vs. GB?

SOLUTION

Cluskitt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cluskitt

You might have to use /1024.0 to get decimal places, actually.

Ioannis Paraskevopoulos

The numerator defines the output data. If the numerator is 100 then the outcome is an Integer. If it is 100.0 then it is a decimal.

This is just to clarify this post.

Giannis

marrowyung

ASKER

harish_varghese,

because the sys.dm_os_volume_stats() only return something in MB but not in GB, so I wnat to have a logic to tell:

1) if the space on each drive available is < 1GB, then the alert only show MB as it is working right now.
2) if the space on each drive available is > 1GB,then the alert show GB instead of MB .

2 x case here.

Cluskitt

There is no place in your script where you output the space of each drive. You only output the threshold (which I've adapted to show MB/GB) and then the drive list. If you want the drives to also have the free space, you'd have to do something like:
SELECT @list = @list + ' ' + Drive + ' ('+CAST(CASE WHEN AvailableMBs>1024 THEN ROUND(AvailableMBs/1024.0,2) ELSE AvailableMBs END AS varchar)+'), '
FROM core
WHERE AvailableMBs < @threshold

ASKER CERTIFIED SOLUTION

Harish Varghese

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marrowyung

ASKER

harish_varghese,

accurate. but i didno't try the MB case, haahha.

Thanks.

Cluskitt,

That script can report free space, I tested it.

I do this now:

 The following drives of ' + @servername+ ' are currently reporting less than ' 
  		+ case when @threshold < 1024 then cast (@threshold as varchar) + ' MB'   -- if space is < 1GB, report in MB.
			else cast (cast (@threshold/1024 as decimal(12,2)) as varchar) + ' GB' end -- if space is > 1GB, report in GB.
  + @list

Open in new window