Windows Disk Space Alert From SQL SERVER JOB

In SQL server when the auto growth option is set to true and when we have defined the autogorwth in MB, some times we may face the issue like windows disk space is full, during that time SQL Server cannot increase the auto growth size of the mdf or ldf files.

As Best practice to have the alert for the drives free space. We can create a job for the below script. schedule it for every 15 or 30 minutes based on your application usage / load.

declare @svrName varchar(255)
declare @sql varchar(400)
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
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)'
into #SpaceGB
from #output
where line like '[A-Z][:]%'
order by drivename
--script to drop the temporary table
--</code>

DECLARE @lowdrives VARCHAR(100)
set @lowdrives= NULL

SELECT @lowdrives = COALESCE(@lowdrives + ' ', '') + drivename + '  freespace(GB):  ' + convert(varchar(50),[freespace(GB)])  FROM #SpaceGB where  [freespace(GB)] <= 50  <span style="color: #ff0000;">--replace your Limit GB</span>
select @lowdrives
if @lowdrives is not NULL
begin

EXEC msdb.dbo.sp_send_dbmail
@profile_name =<span style="color: #ff0000;"> '    ', --- replace with profile name</span>
@recipients = '<span style="color: #ff0000;">  ', --- replace with receiver Mail id</span>
@subject = 'Emergency ** SQL SERVER ** DRIVE SPACE ISSUE' ,
@body =  @lowdrives
end

drop table #output
drop table #SpaceGB

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s