Wednesday 21 May 2014

Query to Find Disk Alllocated and Free space on Server


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