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