Monitoring Disk Space and Sending Alerts with TSQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Haidong Ji » Monitoring Disk Space and Sending Alerts with...

38 posts, Page 4 of 4

«««1 2 3 4

Monitoring Disk Space and Sending Alerts with TSQL

Rate Topic

Display Mode

Topic Options

Author

Message

daniel-471430

Posted Thursday, April 17, 2008 4:54 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 06, 2011 4:09 AM
Points: 4, Visits: 13

hi bob
i have exactly the problem you described here.
we are using mountpoints with an amount of 100 tb and more...
so now i want to find out the size and free space of all mountpoints in all servers we have got.
i've tried wmi but as you've written... it is not working properly.
maybe you can help me/us in that case?
thanks in advance
daniel (vienna)

Post #486275

Bennett Scharf

Posted Thursday, June 25, 2009 12:22 PM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:49 AM
Points: 26, Visits: 407

Here's a simple script for getting volume capacity and free space info when you have mount points. It uses a combination of WMI, ADO, and VBSCript. You can trigger the script via a SQLAgent job or the Windows Task Scheduler. I have it running as a daily SQLAgent job. It populates a table that has date, host name, volume name, capacity, and free space columns.

You'll need to modify this script to suit your particular environment and needs. I apologize that this is not a nice, clean SQL script, but I'm a sysadmin-turned-DBA and these are the tools I had available.

Option Explicit
'
' *********** Modify the following constants to suit your environment ******************

Const strRegPath = "HKLM\Software\scharfco\servers" 'REG_MULTI_SZ value containing names of servers to check
Const strConn = "Provider=SQLOLEDB;Data Source=MyServer;Trusted_Connection=Yes;Initial Catalog=MyDatabase"

' ***************************************************************************************
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim i
Dim strComputer, objWMIService, colItems, objItem
Dim WshShell
Dim objConnection, objCmd
dim strServer, strVolume, intCapacity, intFreeSpace
Set WshShell = Wscript.CreateObject("WScript.Shell")

'Get server list from the Windows registry
'you could also pull your server list from a table
Dim strServerList
strServerList = WshShell.RegRead(strRegPath)

Set objConnection = CreateObject("ADODB.Connection")
set objCmd = CreateObject("ADODB.Command")

objConnection.Open strConn
objCmd.activeConnection = objConnection

For i = 0 To Ubound(strServerList)
If isAlive(strServerList(i)) Then
Set objWMIService = GetObject("winmgmts:\\" & strServerList(i) & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_Volume where DriveType = '3'")
For Each objItem In colItems
'WScript.Echo strServerList(i) & "," & "," & objItem.Name & "," & int(objItem.Capacity / 1048576) & "," & int(objItem.FreeSpace / 1048576)
strServer = strServerList(i)
strVolume = objItem.Name
intCapacity = int(objItem.Capacity / 1048576)
intFreeSpace = int(objItem.FreeSpace / 1048576)
wscript.echo strServer, strVolume, intCapacity, intFreeSpace
objCmd.commandtext = "exec WriteVolumeStats @Server = '" & strServer & "', @VolumeID = '" & strVolume & "', @Capacity = " & intCapacity & ", @FreeSpace = " & intFreeSpace
'objCmd.execute
Next
End if
Next

Function IsAlive(strTarget)
'Pings the target machine. Returns 1 if the machine responds; 0 otherwise
dim WshExec, strPingResults
Set WshShell = WScript.CreateObject("WScript.Shell")
Set WshExec = WshShell.Exec("ping -n 1 -w 2000 " & strTarget)
strPingResults = LCase(WshExec.StdOut.ReadAll)
If InStr(strPingResults, "reply from") Then
IsAlive = True
Else
IsAlive = False
End If
End Function

Post #742100

Bryan_299

Posted Friday, October 16, 2009 1:42 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, December 11, 2009 12:35 PM
Points: 2, Visits: 21

I am trying to set up a SSIS Package where this sql query will run through all of my servers. (this part is done)
I set that up to do a performance test on all the servers,

however now I am trying to check disk space on all 128 servers as well.

This query is not working for me though. I believe it is due to the temp. tables possibly?

Can someone help me?

Thanks,

Post #804498

kishore.kodukulla-non-empl

Posted Wednesday, December 08, 2010 11:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 2:03 PM
Points: 1, Visits: 177

Hello Haidong Ji,

How can i change the script into percentage free.In you script you mentioned a hard limit of 2GB,but i want it in percentage.Say if the drive is 10 % free then send the alert.Can you please post that.

Thanks In Advance

Post #1032048

SQLEngine

Posted Tuesday, November 22, 2011 8:23 PM

Valued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 11:29 AM
Points: 53, Visits: 224

Bob, would you mind sharing how you check mount point free space with CHKDSK in Tsql ?

Post #1210651

M P-486151

Posted Sunday, January 29, 2012 7:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 23, 2012 10:58 AM
Points: 6, Visits: 70

Hi Bob very good answer.
Could you please post the code of your working solution?
Thanks

Post #1243291

Michael-401546

Posted Tuesday, February 28, 2012 1:59 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 11:24 AM
Points: 10, Visits: 223

Here is the code I use to get mount point information. I'm sure someone has a better way but I couldn't find it
/*
This code will go out to the O/S and check for free space at the drive level
It will only check volumes that have database files (mdf,ldf,ndf) (sysaltfiles)
It will only check volumes attached to the node
*/
Declare @svrName varchar(255)
, @sql varchar(400)
, @Path varchar(400)
, @Label varchar(400)
, @Capacity Decimal(12,2)
, @FreeSpace Decimal(12,2)
, @PercentFree Decimal(12,2)
, @Row int
, @MessageBody NVARCHAR(MAX)
, @Subject NVARCHAR(250)
--
Declare @output TABLE
( row int IDENTITY(1,1) NOT NULL
, line varchar(255) )
--
SET NOCOUNT ON
--
BEGIN -- use powershell to go ou to the O/S and get a list of all volumes attached to the server
set @sql = 'powershell.exe -c "gwmi win32_volume'
+ '|'
+ 'where-object {$_.filesystem -match ''ntfs''}'
+ '|'
+ 'format-list name,capacity,freespace,label"'
--
insert @output
EXEC xp_cmdshell @sql
--
Delete @output where line is null

END
--
WHILE EXISTS ( SELECT *
FROM @OutPut
where line like 'name : %'
or line like 'capacity : %'
or line like 'label : %'
OR line like 'freespace : %' )
BEGIN
SELECT TOP 1
@Row = row
, @Path = substring(Line,13,len(Line))
FROM @Output
WHERE Line like 'name : %'
DELETE @Output
WHERE row = @Row
--
SELECT TOP 1
@Row = row
, @Label = substring(Line,13,len(Line))
FROM @Output
WHERE Line like 'label : %'
DELETE @Output
WHERE row = @Row
--
SELECT TOP 1
@Row = row
, @Capacity = cast(substring(Line,13,len(line)) as bigint)/1048576.00
FROM @Output
WHERE Line like 'capacity : %'
DELETE @Output
WHERE row = @Row
--
SELECT TOP 1
@Row = row
, @FreeSpace = cast(substring(line,13,len(line)) as bigint)/1048576.00
FROM @Output
WHERE Line like 'freespace : %'
DELETE @Output
WHERE row = @Row
--
SET @PercentFree = cast( ( ( ( @Capacity - ( @Capacity - @FreeSpace ) ) / @Capacity ) * 100.00 ) as decimal(12,2) )
--
IF ( @PercentFree < 6
and @Path not like '\\?\Volume%'
and @Path in ( select reverse(substring(reverse(filename),charindex('\',reverse(filename)),100)) from master.dbo.sysaltfiles) )
BEGIN -- Ready email body
Select @MessageBody = 'Disk Space Alert'
+ char(10) + char(13)
+ ' Computer Name = ' + cast(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as nvarchar(25))
+ char(10) + char(13)
+ ' Instance Name = ' + cast(@@servername as nvarchar(50))
+ char(10) + char(13)
+ ' Drive or Path = ' + @Path
+ char(10) + char(13)
+ ' Label = ' + @Label
+ char(10) + char(13)
+ ' Capacity (MB) = ' + cast(@Capacity as nvarchar(20))
+ char(10) + char(13)
+ ' Free Space (MB) = ' + cast(@FreeSpace as nvarchar(20))
+ char(10) + char(13)
+ ' %Free Space = ' + cast(@PercentFree as nvarchar(20))
+ char(10) + char(13)
+ ' EventTime = ' + convert(varchar, getdate())
+ char(10) + char(13)
--
BEGIN -- Send Email
SET @SUBJECT = 'SQL Monitor Disk Space Alert: '+ @Path + ' is at ' + cast(@PercentFree as nvarchar(20)) + '% free'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLDBA Profile'
, @recipients = 'My.Email@MyCompany.com'
, @SUBJECT = @Subject
, @Body = @MessageBody
, @importance = 'High'
, @exclude_query_output = 1
END
--
END
END

Post #1259135

juan Munoz

Posted Friday, April 05, 2013 4:27 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:38 PM
Points: 10, Visits: 161

Ji Haidong,

Would you have a Mount Point version of this script?
Here is what I use now with out the email feature.
Maybe it can be used as a good starting point.

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
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 'capacity(MB)'
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'
from #output
where line like '[A-Z][:]%'
order by drivename
--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

Post #1439526

« Prev Topic | Next Topic »

38 posts, Page 4 of 4

«««1 2 3 4

Permissions