SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Monitoring Disk Space and Sending Alerts with TSQL


Monitoring Disk Space and Sending Alerts with TSQL

Author
Message
daniel-471430
daniel-471430
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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)
Bennett Scharf
Bennett Scharf
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 602
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
Bryan_299
Bryan_299
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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,
kishore.kodukulla-non-empl
kishore.kodukulla-non-empl
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
SQLEngine
SQLEngine
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 236
Bob, would you mind sharing how you check mount point free space with CHKDSK in Tsql ?
M P-486151
M P-486151
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 76
Hi Bob very good answer.
Could you please post the code of your working solution?
Thanks
Michael-401546
Michael-401546
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 348
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
Joe Zonum
Joe Zonum
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 461
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
cjohnson 94918
cjohnson 94918
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 13
I had to tweak it a little for it to work for me. I had to add the @profile_name for my sp_send_dbmail to work.

I have the stored procedure saved under master db. I assume this is the best place?

Then the next step is to put this in an SQL agent job to execute on a frequency.

Thanks!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search