Disk Space Alerts

/* Disk Space Alerts */
CREATE proc [dbo].[USP_Send_DiskSpace]
--
(
@To varchar(200) ,
@CRITICAL int = 10 -- if the freespace(%) is less than @alertvalue, it will send message
)
as
Begin
DECLARE @HOSTNAME VARCHAR(20),
@HEAD VARCHAR(100),
@BGCOLOR VARCHAR(50),
@REC VARCHAR(50),
@PRIORITY VARCHAR(10),
@FREE VARCHAR(20),
@TOTAL VARCHAR(20),
@FREE_PER VARCHAR(20),
@CHART VARCHAR(2000),
@HTML VARCHAR(MAX),
@HTMLTEMP VARCHAR(MAX),
@TITLE VARCHAR(100),
@DRIVE VARCHAR(100),
@SQL VARCHAR(MAX)

CREATE TABLE #MOUNTVOL (COL1 VARCHAR(500))

INSERT INTO #MOUNTVOL
EXEC XP_CMDSHELL 'MOUNTVOL'

DELETE #MOUNTVOL WHERE COL1 NOT LIKE '%:%'
DELETE #MOUNTVOL WHERE COL1 LIKE '%VOLUME%'
DELETE #MOUNTVOL WHERE COL1 IS NULL
DELETE #MOUNTVOL WHERE COL1 NOT LIKE '%:%'
DELETE #MOUNTVOL WHERE COL1 LIKE '%MOUNTVOL%'
DELETE #MOUNTVOL WHERE COL1 LIKE '%RECYCLE%'

SELECT LTRIM(RTRIM(COL1)) FROM #MOUNTVOL

CREATE TABLE #DRIVES
(
DRIVE VARCHAR(500),
INFO VARCHAR(80)
)

DECLARE CUR CURSOR FOR SELECT LTRIM(RTRIM(COL1)) FROM #MOUNTVOL
OPEN CUR
FETCH NEXT FROM CUR INTO @DRIVE
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL = 'EXEC XP_CMDSHELL ''FSUTIL VOLUME DISKFREE ' + @DRIVE +''''

INSERT #DRIVES
(
INFO
)
EXEC (@SQL)

UPDATE #DRIVES
SET DRIVE = @DRIVE
WHERE DRIVE IS NULL

FETCH NEXT FROM CUR INTO @DRIVE
END
CLOSE CUR
DEALLOCATE CUR

-- SHOW THE EXPECTED OUTPUT
SELECT DRIVE,
SUM(CASE WHEN INFO LIKE 'TOTAL # OF BYTES : %' THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TOTALSIZE,
SUM(CASE WHEN INFO LIKE 'TOTAL # OF FREE BYTES : %' THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FREESPACE
INTO #DISKSPACE FROM (
SELECT DRIVE,
INFO
FROM #DRIVES
WHERE INFO LIKE 'TOTAL # OF %'
) AS D
GROUP BY DRIVE
ORDER BY DRIVE

SET @TITLE = 'DISK SPACE REPROT : '+ @@SERVERNAME

SET @HTML = '<HTML><TITLE>'+@TITLE+'</TITLE>
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=2>
<TR BGCOLOR=#0070C0 ALIGN=CENTER STYLE=''FONT-SIZE:8.0PT;FONT-FAMILY:"TAHOMA","SANS-SERIF";COLOR:WHITE''>
<TD WIDTH=40>DRIVE</TD>
<TD WIDTH=250>TOTAL</TD>
<TD WIDTH=150>FREE SPACE</TD>
<TD WIDTH=150>FREE PRECENTAGE</TD>
</TR>'

DECLARE RECORDS CURSOR
FOR SELECT CAST(DRIVE AS VARCHAR(100)) AS 'DRIVE', CAST(FREESPACE/1024/1024 AS VARCHAR(10)) AS 'FREE',CAST(TOTALSIZE/1024/1024 AS VARCHAR(10)) AS 'TOTAL',
CONVERT(VARCHAR(2000),'<TABLE BORDER=0 ><TR><TD BORDER=0 BGCOLOR='+ CASE WHEN ((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0 < @CRITICAL
THEN 'RED'
WHEN ((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0 > 70
THEN '66CC00'
ELSE
'0033FF'
END +'><IMG SRC=''/GIFS/S.GIF'' WIDTH='+CAST(CAST(((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0*2 AS INT) AS CHAR(10) )+' HEIGHT=5></TD>
<TD>'+CAST(CAST(((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0 AS INT) AS CHAR(10) )+'%</TD></TR></TABLE>') AS 'CHART'
FROM #DISKSPACE ORDER BY ((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0

OPEN RECORDS

FETCH NEXT FROM RECORDS INTO @DRIVE , @FREE, @TOTAL, @CHART

WHILE @@FETCH_STATUS = 0

BEGIN

SET @HTMLTEMP =
'<TR BORDER=0 BGCOLOR="#E8E8E8" STYLE=''FONT-SIZE:8.0PT;FONT-FAMILY:"TAHOMA","SANS-SERIF";COLOR:#0F243E''>
<TD ALIGN = CENTER>'+@DRIVE+'</TD>
<TD ALIGN=CENTER>'+@TOTAL+'</TD>
<TD ALIGN=CENTER>'+@FREE+'</TD>
<TD VALIGN=MIDDLE>'+@CHART+'</TD>
</TR>'

SET @HTML = @HTML + @HTMLTEMP

FETCH NEXT FROM RECORDS INTO @DRIVE , @FREE, @TOTAL, @CHART

END
CLOSE RECORDS
DEALLOCATE RECORDS

SET @HTML = @HTML + '</TABLE> 
THANKS,
DBA TEAM
</HTML>'

--PRINT
PRINT @HTML

--save data
if(object_id('DBA.dbo.diskdrive_stats') is null)
Begin
create table DBA.dbo.diskdrive_stats (
Drive varchar(100) ,
FreeSpace float null,
TotalSize float null,
Free_per float,
date_time datetime)

insert into DBA.dbo.diskdrive_stats (Drive,Freespace,TotalSize,Free_Per,date_time)
select Drive,convert(float,freespace),convert(float,totalsize),
convert(float,((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0),getdate() from #DISKSPACE

--insert into DBA.dbo.diskdrive_stats (Drive,Freespace,TotalSize,Free_Per,date_time)
--select *,((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0,getdate() from #DISKSPACE
End
Else
Begin
insert into DBA.dbo.diskdrive_stats (Drive,Freespace,TotalSize,Free_Per,date_time)
select Drive,convert(float,freespace),convert(float,totalsize),
convert(float,((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0),getdate() from #DISKSPACE
End

--############################Send Mail#############################

set @head = '<RED> Disk Space report from SQL Server : '+@@servername

--SELECT * FROM #DISKSPACE

IF EXISTS(SELECT * FROM #DISKSPACE WHERE CAST((FREESPACE/(TOTALSIZE*1.0))*100.0 AS INT) <= @CRITICAL)
BEGIN
SET @PRIORITY = 'HIGH'

print @head
exec msdb.dbo.sp_send_dbmail
@profile_name = 'SQLProfile',
@recipients = @To,
@subject = @head,
@importance = @Priority,
@body = @HTML,
@body_format = 'HTML'

END
ELSE
BEGIN
print''
END

DROP TABLE #MOUNTVOL
DROP TABLE #DRIVES
DROP TABLE #DISKSPACE

END

Search This Blog

MSBI Tutor

Disk Space Alerts

Comments

Post a Comment

Popular Posts

Failed to execute the package or element. Build errors were encountered

Exception deserializing the package "The process cannot access the file because it is being used by another process."

Temporary enable and disable SSRS subscriptions