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

avatar image

tombiernacki
338 20 22 27

(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

avatar image

dosberg
132 10 10 14

(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

avatar image

basit 1
509 56 65 91

(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.

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:

x1067
x9

asked: Aug 21, 2012 at 08:27 PM

Seen: 908 times

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

Copyright 2016 Redgate Software. Privacy Policy