x

Need help creating script that generates total diskspace

Hi I need assistance creating a script that generates total disk space on disk drives. Also I would like to omit drive letter 'K' if it exists. Once I get that I want to add logic that if free space is less than 10% of total size than generate a warning email.

here is what I found;

DECLARE @psinfo TABLE(data  NVARCHAR(100)) ;
INSERT INTO @psinfo
EXEC xp_cmdshell 'Powershell.exe "Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"| Format-Table DeviceID, FreeSpace, Size"'  ;
DELETE FROM @psinfo WHERE data is null  or data like '%DeviceID%' or data like '%----%';
update @psinfo set data = REPLACE(data,' ',',');


;With DriveSpace as (


select SUBSTRING(data,1,2)  as [Drive], 
replace((left((substring(data,(patindex('%[0-9]%',data)) , len(data))),CHARINDEX(',',
(substring(data,(patindex('%[0-9]%',data)) , len(data))))-1)),',','')
as [FreeSpace]
, 
replace(right((substring(data,(patindex('%[0-9]%',data)) , len(data))),PATINDEX('%,%', 
(substring(data,(patindex('%[0-9]%',data)) , len(data))))) ,',','')
as [Size]
from @psinfo
) 
SELECT Drive, convert(dec( 6,2),CONVERT(dec(17,2),FreeSpace)/(1024*1024*1024)) 
as FreeSpaceGB, convert(dec( 6,2),CONVERT(dec(17,2), size)/(1024*1024*1024)) as SizeGB
FROM DriveSpace;

This works great because it gives me total disk size and free space available but I am not sure how to omit drive 'k' if it exist also how to add logic that if free space is less than 10% of total size than generate a warning email.

more ▼

asked Aug 21, 2012 at 08:27 PM in Default

tombiernacki gravatar image

tombiernacki
338 18 20 23

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
instead of xp_cmdshell take a look at xp_fixeddrives.
more ▼

answered Aug 22, 2012 at 03:41 AM

dosberg gravatar image

dosberg
122 9 10 12

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Aug 22, 2012 at 02:34 PM

basit 1 gravatar image

basit 1
499 51 61 84

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985
x6

asked: Aug 21, 2012 at 08:27 PM

Seen: 683 times

Last Updated: Aug 22, 2012 at 02:34 PM