question

tombiernacki avatar image
tombiernacki asked

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.
t-sqldisk-space
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dosberg avatar image
dosberg answered
instead of xp_cmdshell take a look at xp_fixeddrives.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

basit 1 avatar image
basit 1 answered
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'

Check Disk Space availibility (threshold= 10%)



' + N' ' + N' Server IPServer Name' + N' DriveTotal Size(GB)Free Space(GB)' + N' Free Space(%)' + 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' ' ; 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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.