CREATE TABLE #T1(
DRVLETTER CHAR(1),
DRVSPACE INT
)

INSERT INTO #T1 EXEC master.dbo.xp_fixeddrives

/* GENERATE THE MESSAGE */

IF (SELECT COUNT(*) FROM #T1) > 0 AND LEN(@RCPT) > 0 --CHECK THERE IS SOME DATA AND A RECIPIENT
BEGIN
DECLARE @MSG VARCHAR(400),
@DLETTER VARCHAR(5),
@DSPACE INT

SET @DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1 --GET FIRST DRIVE LETTER
WHERE DRVSPACE < @LIMIT 
ORDER BY DRVLETTER ASC)

SET @DSPACE = (SELECT DRVSPACE FROM #T1 --GET THE DISK SPACE FOR THE LETTER
WHERE DRVLETTER = @DLETTER)

SET @MSG =convert(varchar(10), getdate(), 104) + ' ' + convert(varchar(10), getdate(), 108) + ' On ' + @@SERVERNAME + ' ' + @DLETTER + ' less  ' + CONVERT(VARCHAR,@DSPACE) --PUT THE VARS INTO A MSG
+ 'MB!!!'-- + CHAR(13) + CHAR(10)


WHILE (SELECT COUNT(*) FROM #T1 WHERE DRVSPACE < @LIMIT AND DRVLETTER > @DLETTER) > 0
BEGIN	 --LOOP THROUGH DRIVE LETTERS AND REPEAT ABOVE	
SET @DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1 
WHERE DRVSPACE < @LIMIT 
AND DRVLETTER > @DLETTER 
ORDER BY DRVLETTER ASC)

SET @DSPACE = (SELECT DRVSPACE FROM #T1 
WHERE DRVLETTER = @DLETTER)
SET @MSG = @MSG + 'On local disk ' + @DLETTER + ' free space is at ' + CONVERT(VARCHAR,@DSPACE) + 'MB' 
+ CHAR(13) + CHAR(10)
END

CREATE function [dbo].[af_DrivesInfo] ()
returns @Drives table (Drive char(1), TotalSize bigint, AvailableSpace bigint)
as begin
/*
	select getdate()
	select Drive, 
		TotalSize = cast(TotalSize/1024./1024./1024. as decimal(10,2)), 
		AvailableSpace = cast(AvailableSpace/1024./1024./1024. as decimal(10,2)) 
		from dbo.af_DrivesInfo()
	select getdate()
*/
	declare @hr integer, @fso integer, @objDrive integer, @Drive char(1), @DriveInt int, 
		@DriveType int, @TotalSizeC varchar(1000), @AvailableSpaceC varchar(18), @TotalSize bigint, @AvailableSpace bigint

	exec @hr = sp_OACreate 'Scripting.FileSystemObject', @fso output

	set @DriveInt = 67

	while @DriveInt <= 90 begin
		set @Drive = CHAR(@DriveInt)
		set @TotalSize = null
		set @AvailableSpace = null

		exec @hr = sp_OAMethod @fso, 'GetDrive', @objDrive out, @Drive
		if @hr = 0 begin
			exec @hr = sp_OAGetProperty @objDrive,'DriveType', @DriveType output
			if @DriveType = 2 begin
				exec @hr = sp_OAGetProperty @objDrive, 'TotalSize', @TotalSizeC output
				exec @hr = sp_OAGetProperty @objDrive, 'AvailableSpace', @AvailableSpaceC output

				insert @Drives (Drive, TotalSize, AvailableSpace)
				values (@Drive, convert(bigint, @TotalSizeC), convert(bigint, @AvailableSpaceC))
			end
			exec sp_OADestroy @objDrive
		end

		set @DriveInt = @DriveInt + 1
	end

	exec sp_OADestroy @fso

	return
end

CREATE procedure [dbo].[sp_diskalert]
@RCPT VARCHAR(500), --Кому отсылать 
@PROCENT FLOAT --Лимит в процентах всего места от свободного (0.1-0.9)

AS
BEGIN
SET NOCOUNT ON
/*
CREATE TABLE #T1(
DRVLETTER CHAR(1),
DRVSPACE INT
)

INSERT INTO #T1 EXEC master.dbo.xp_fixeddrives
*/

--используя функцию заполняем времянку

select Drive as DRVLETTER,( TotalSize/1024/1024) as TOTALDRVSRACE, (AvailableSpace /1024/1024) as DRVSPACE
into #T1
from  dbo.af_DrivesInfo()

--генерируем сообщение

IF (SELECT COUNT(*) FROM #T1) > 0 AND LEN(@RCPT) > 0 --CHECK THERE IS SOME DATA AND A RECIPIENT
BEGIN
DECLARE @MSG VARCHAR(400),
@DLETTER VARCHAR(5),
@DSPACE INT

SET @DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1 --GET FIRST DRIVE LETTER
WHERE DRVSPACE <  TOTALDRVSRACE*@PROCENT
ORDER BY DRVLETTER ASC)

SET @DSPACE = (SELECT DRVSPACE FROM #T1 --GET THE DISK SPACE FOR THE LETTER
WHERE DRVLETTER = @DLETTER)

SET @MSG =convert(varchar(10), getdate(), 104) + ' ' + convert(varchar(10), getdate(), 108) + ' On ' + @@SERVERNAME + ' ' + @DLETTER + ' less  ' + CONVERT(VARCHAR,@DSPACE) --PUT THE VARS INTO A MSG
+ 'MB!!!'-- + CHAR(13) + CHAR(10)


WHILE (SELECT COUNT(*) FROM #T1 WHERE DRVSPACE < TOTALDRVSRACE*@PROCENT AND DRVLETTER > @DLETTER) > 0
BEGIN	 --LOOP THROUGH DRIVE LETTERS AND REPEAT ABOVE	
SET @DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1 
WHERE DRVSPACE < TOTALDRVSRACE*@PROCENT
AND DRVLETTER > @DLETTER 
ORDER BY DRVLETTER ASC)

SET @DSPACE = (SELECT DRVSPACE FROM #T1 
WHERE DRVLETTER = @DLETTER)
SET @MSG = @MSG + 'On local disk ' + @DLETTER + ' free space is at ' + CONVERT(VARCHAR,@DSPACE) + 'MB' 
+ CHAR(13) + CHAR(10)
END



--посылаем сообщение

IF CHARINDEX('@',@RCPT) > 0 --THERE IS AN @ SYMBOL IN THE RECIPIENT - SEND EMAIL
BEGIN
DECLARE @EMAIL VARCHAR(600)


--=====================================================

SET @EMAIL = 'EXEC msdb.dbo.sp_send_dbmail
@profile_name = admin, 
@recipients = ''' + @RCPT + ''', 
@body = ''' + @MSG + ''', 
@subject = ''!! LOW FREE DISK SPACE ON ' + @@SERVERNAME + ' !!'''
EXEC (@EMAIL)
END


END

DROP TABLE #T1

END