365DBA Blog

Database Stuff

SQL Server Monitoring Scripts - 2 - Low Disk Space

The second monitoring script in this series is a simple script for obtaining disk space information and alerting if it is below a given threshold. Once again, the formatting is specific to the purpose of using it with the remote PowerShell script which is the reason for the formatting.

Set NoCount On
Set ANSI_Warnings Off
Select '---------------Disk Space Check---------------'
Declare @Threshold Int
Set @Threshold = 30720 --30 GB
Declare @AllDrives Table (drive Char(1), [MB free] Int)
Declare @LowDrives Table (ID Int Identity, Drive Char(1), MBFree Int)
Insert Into @AllDrives
Exec master.dbo.xp_fixeddrives
Insert Into @LowDrives (Drive, MBFree)
Select drive, [MB free] From @AllDrives Where [MB free] <= @Threshold
If (Select Count(*) From @LowDrives) > 0
Begin
Declare @Count Int, @Counter Int
Select @Count = Max(ID) From @LowDrives
Select @Counter = Min(ID) From @LowDrives
While @Counter <= @Count
Begin
Select 'Server: ' + Cast(ServerProperty('MachineName') As sysname) + IsNull('\' + Cast(ServerProperty('InstanceName') As sysname), '') + 
' low disk space: ' + Drive + ' = ' + Cast(MBFree As VarChar(10)) + ' is below threshold of ' + Cast(@Threshold As VarChar(10))
From @LowDrives Where ID = @Counter
Set @Counter = @Counter + 1
End
End
Loading