Need help creating script that generates total diskspace

instead of xp_cmdshell take a look at xp_fixeddrives.

You can use the below Script to get the alert when you are facing low space issue

Create first Table to store the data


CREATE TABLE [dbo].[ILFS_DRIVES](
 [ExecDate] [datetime] NULL,
 [drive] [char](1) NULL,
 [TotalSize] [int] NULL,
 [FreeSpace] [int] NULL,
 [Per_Free] [int] NULL,
 [Servername] [char](100) NULL,
 [ServerIP] [char](100) NULL,
 [backup_size] [numeric](19, 2) NULL,
 [count_db] [int] NULL,
 [flag] [int] NULL,
 [count_backupfile] [int] NULL,
 [Remark] [varchar](50) NULL
) ON [PRIMARY]

GO



*****************************

Procedure to check the space






USE [master]
GO
/****** Object:  StoredProcedure [dbo].[usp_DiskFreeSpaceAlert]    Script Date: 08/22/2012 09:28:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--[usp_DiskFreeSpaceAlert] 10
ALTER PROCEDURE [dbo].[usp_DiskFreeSpaceAlert] (@MINDISKSPACE INT)  
AS  
 /* ---   
 BASE TABLE : SELECT * FROM  ILFS_DRIVES ORDER BY 1 DESC         
 EXECUTION : EXEC ILFS_SP_DISKSPACE_V4 100  
 -- */  
SET NOCOUNT ON  

DECLARE  @HR INT                    
DECLARE  @FSO INT                      
DECLARE  @DRIVE CHAR(1)                      
DECLARE  @ODRIVE INT                      
DECLARE  @TOTALSIZE VARCHAR(20)                      
DECLARE  @MB BIGINT    

DECLARE  @MFREESPACE INT          
DECLARE  @MBACKUPSIZE INT          

DECLARE  @QUERYSTRING NVARCHAR(100)                
DECLARE  @MCOUNT_DB INT          
DECLARE  @MCOUNT_BACKUPFILE INT          

SET @MFREESPACE =0          
SET @MBACKUPSIZE =0          

SET @MCOUNT_DB =0          
SET @MCOUNT_BACKUPFILE =0          

SET @MB = 1048576          

-- CHECKS AND DELETES FOR CURRENT DAY RECORDS --           

DELETE FROM  ILFS_DRIVES         


-- INSERTS DRIVE AND FREESPACE, INTO TABLE --           

SET @QUERYSTRING = 'MASTER.DBO.XP_FIXEDDRIVES'           

INSERT INTO  ILFS_DRIVES (DRIVE, FREESPACE)         
EXEC @QUERYSTRING        

-- UPDATE DATE AS CURRENT SYSTEM DATE --      

UPDATE  ILFS_DRIVES SET EXECDATE = CONVERT(VARCHAR,GETDATE(),102)         
WHERE         
 EXECDATE IS NULL           

-- UPDATES THE SERVERNAME --           

UPDATE  ILFS_DRIVES SET SERVERNAME = @@SERVERNAME ,serverip= @@SERVERNAME      
WHERE      
 CONVERT(VARCHAR,EXECDATE,102) = CONVERT(VARCHAR,GETDATE(),102)           



-- UPDATES TOTALSIZE, PER_FREE --------------------------------------------------------------------------------          

EXEC @HR = SP_OACREATE 'SCRIPTING.FILESYSTEMOBJECT', @FSO OUT                    

IF @HR <> 0 EXEC SP_OAGETERRORINFO @FSO                    

DECLARE DCUR CURSOR LOCAL FAST_FORWARD FOR 
 SELECT DRIVE FROM  ILFS_DRIVES 
 WHERE CONVERT(VARCHAR,EXECDATE,102) = CONVERT(VARCHAR,GETDATE(),102) 
 ORDER BY DRIVE

OPEN DCUR                      
FETCH NEXT FROM DCUR INTO @DRIVE                    

WHILE @@FETCH_STATUS = 0                    
BEGIN                    
 EXEC @HR = SP_OAMETHOD @FSO ,'GETDRIVE' ,@ODRIVE OUT ,@DRIVE                    

 IF @HR <> 0                  
 EXEC SP_OAGETERRORINFO @FSO                    
 EXEC @HR = SP_OAGETPROPERTY @ODRIVE ,'TOTALSIZE' ,@TOTALSIZE OUT        

 IF @HR <> 0 EXEC SP_OAGETERRORINFO @ODRIVE                    

 UPDATE  ILFS_DRIVES SET TOTALSIZE = @TOTALSIZE/@MB         
 WHERE         
  DRIVE = @DRIVE AND         
  CONVERT(VARCHAR,EXECDATE,102) = CONVERT(VARCHAR,GETDATE(),102) AND         
  SERVERNAME = @@SERVERNAME        

 FETCH NEXT FROM DCUR INTO @DRIVE                    
END                    

CLOSE DCUR                      
DEALLOCATE DCUR                    

EXEC @HR = SP_OADESTROY @FSO                    
IF @HR <> 0 EXEC SP_OAGETERRORINFO @FSO                    

UPDATE  ILFS_DRIVES                  
SET  TOTALSIZE= TOTALSIZE,         
 FREESPACE= FREESPACE,         
 PER_FREE= CAST((FREESPACE/(TOTALSIZE*1.0))*100.0 AS INT)                  
WHERE         
 CONVERT(VARCHAR,EXECDATE,102)= CONVERT(VARCHAR,GETDATE(),102) AND         
 SERVERNAME = @@SERVERNAME          



-- DELETES IF EXECDATE IS NULL --          

DELETE FROM  ILFS_DRIVES         
WHERE         
 EXECDATE IS NULL  
update  ILFS_DRIVES set flag=0
--please enter the drive letter  in below query to not get alert
update  ILFS_DRIVES set flag=1 where drive in ('')


--- CHECKS IF ANY DRIVE IS LESS THAN "MIN DISK SPACE" (VALUE SET = 20) ----------------------------------------          

IF (SELECT COUNT(1) FROM  ILFS_DRIVES WHERE CONVERT(VARCHAR,EXECDATE,102)=CONVERT(VARCHAR,GETDATE(),102) AND SERVERNAME = @@SERVERNAME AND PER_FREE <=@MINDISKSPACE)>0          
 SELECT SERVERIP, DRIVE, TOTALSIZE, FREESPACE, PER_FREE, BACKUP_SIZE, COUNT_DB AS COUNT_DATAFILES, COUNT_BACKUPFILE, REMARK, EXECDATE, SERVERNAME          
 FROM  ILFS_DRIVES           
 WHERE        
  CONVERT(VARCHAR,EXECDATE,102)=CONVERT(VARCHAR,GETDATE(),102)        
  AND SERVERNAME = @@SERVERNAME        
  AND PER_FREE <=@MINDISKSPACE    

DECLARE @MPOFILENAME VARCHAR(100)                
DECLARE @MPROFILEDESC VARCHAR(100)          
DECLARE @MQUERY VARCHAR(5000)            
DECLARE @MRECIPIENTS VARCHAR(200)          



SET @MRECIPIENTS = 'CAPGEMINI_DBA@us.astellas.com'  





 --- EMAIL : JOB FAILURE : HTML FORMAT ----------------------------------------------------------------  

 IF EXISTS(SELECT * FROM  ILFS_DRIVES WHERE CONVERT(VARCHAR,EXECDATE,112) >= CONVERT(VARCHAR,getdate(),112) AND PER_FREE<=@MINDISKSPACE)  
 BEGIN  
  DECLARE @tableHTML  NVARCHAR(MAX)  
  SET @MPROFILEDESC = 'Disk Space availibility, threshold 10% for Date :- ' + convert(varchar,getdate(),100)  

  SET @tableHTML =   
  N'<H2><I>Check Disk Space availibility (threshold= 10%)</I></H2><BR><BR>' +  
  N'<table border="1" cellpadding="2" cellspacing="0" width="100%">' +  
  N'<tr bgcolor="RED"><th nowrap="nowrap">Server IP</th><th nowrap="nowrap">Server Name</th>' +  
  N'<th>Drive</th><th>Total Size(GB)</th><th>Free Space(GB)</th>' +  
  N'<th nowrap="nowrap">Free Space(%)</th></tr>' +  
  CAST ( ( SELECT td = LEFT(SERVERNAME,20),       '',  
      td = LEFT(SERVERNAME,30), '',  
      td = Drive, '',  
      td = TotalSize/1024, '',  
      td = FreeSpace/1024, '',  
      td = Per_Free  
      FROM  ILFS_DRIVES  
      WHERE CONVERT(VARCHAR,EXECDATE,112) = CONVERT(VARCHAR,GETDATE(),112) AND   
      PER_FREE<=@MINDISKSPACE AND isnull(FLAG,0) =0  
      ORDER BY SERVERNAME  
      FOR XML PATH('tr'), TYPE  
  ) AS NVARCHAR(MAX) ) +  
  N'</table>' ;  

  EXEC MSDB.DBO.SP_SEND_DBMAIL                
   @PROFILE_NAME = 'SQL2005_Email_Profile',                
   @RECIPIENTS = @MRECIPIENTS,                
   @SUBJECT = @MPROFILEDESC ,                
   @body = @tableHTML,  
   @body_format = 'HTML'  

  END


select * from ILFS_DRIVES 

Once the SP Created created a job and execute the procedre by passing the value min disk %

like Exec usp_DiskFreeSpaceAlert 10