Disk Space Alert and Report
Below query is useful to get an report to Mail like each drive info as in bottom of the page.
You need to replace DB Mail and Mail ID in below Script to get Report.
We can execute this job as response to any Disk Space Alert so if there disk space issue Alert will trigger job to execute and Report will be send to MailID in script replaced,
If you need to creates Disk space Alert please find the Link
http://myexperienceinmssql.blogspot.com/2014/04/sql-alert-for-disk-space-issues.html
Script requires XP_CMDShell to be enabled on the server.
Set NOcount on
declare @svrName varchar(255)
declare @sql varchar(400)
--by default it will take the current
server name, we can the set the server name as well
set @svrName = @@SERVERNAME
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName '
+ QUOTENAME(@svrName,'''') + ' -Class Win32_Volume
-Filter ''DriveType = 3'' | select name,capacity,freespace |
foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
--creating a temporary table
CREATE TABLE #output
(line varchar(255))
--inserting disk name, total space and
free space value in to temporary table
insert #output
EXEC xp_cmdshell @sql
--script to retrieve the values in MB
from PS Script output
CREATE TABLE #DiskInfo (DriveName NVarchar(100),DiskAllocated NVarchar(100),Freespace NVarchar(100))
Insert into #DiskInfo
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
(CHARINDEX('%',line)
-1)-CHARINDEX('|',line)) )) as Float),0) as DiskAllocated
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line)
-1)-CHARINDEX('%',line)) )) as Float),0) as Freespace
from #output
where line like '[A-Z][:]%'
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @Subject NVARCHAR(300)
SET @Subject='Disk Space issue Alert on Server '+(select @@servername)+''
SET @xml = CAST((select Drivename AS 'td','', DiskAllocated AS 'td','',Freespace AS 'td' from #DiskInfo
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body =
N'<H4>Disk Space issue Report on server '+ (select
td = (select @@servername))+' </H4>'+
N'<H5> Alert is raised because Disk
free space is less than threshold values Set on the Server </H5>'+
N'<H5>Please look into this.
</H5>'+
N'<html><body><table border
= 1> <FONT SIZE=2>
<tr
bgcolor=#F8F8FD>
<th> <FONT SIZE=2>Drive Name
</FONT></th> <th><FONT SIZE=2> DiskAllocated in MB
</FONT></th> <th><FONT SIZE=2> Free Space in MB
</FONT></th>
</tr>'
SET @body = @body + @xml +'</FONT></table></body></html>'
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB Mail',
-- replace with your SQL Database Mail
Profile
@body = @body,
@body_format ='HTML',
@recipients = 'SQLdbaLearner@gmail.com',
-- replace with your email address
@subject = @Subject ;
END
GO
/*--script to retrieve the values in GB
from PS Script output
select
rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as
'capacity(GB)'
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as
'freespace(GB)'
from #output
where line like '[A-Z][:]%'
order by drivename
*/
--script to drop the temporary table
drop table #output
Drop table #DiskInfo
Ex: Mail body will be as below:
___________________________________________________________________________
Alert is raised because Disk free space is less than threshold values Set on the Server
Please look into this.
Drive Name
|
DiskAllocated in GB
|
Free Space in GB
|
C:\
|
40
|
8
|
D:\
|
140
|
46
|
E:\
|
320
|
9
|
F:\
|
100
|
80
|
No comments:
Post a Comment