Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Monitoring Disk Space and Sending Alerts with TSQL Expand / Collapse
Author
Message
Posted Thursday, April 17, 2008 4:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 6, 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
Posted Thursday, June 25, 2009 12:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:29 AM
Points: 28, Visits: 491
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
Posted Friday, October 16, 2009 1:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, December 8, 2010 11:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, November 22, 2011 8:23 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 8:03 AM
Points: 53, Visits: 232
Bob, would you mind sharing how you check mount point free space with CHKDSK in Tsql ?
Post #1210651
Posted Sunday, January 29, 2012 7:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 27, 2014 4:23 AM
Points: 6, Visits: 72
Hi Bob very good answer.
Could you please post the code of your working solution?
Thanks
Post #1243291
Posted Tuesday, February 28, 2012 1:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 9:39 AM
Points: 12, Visits: 295
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
Posted Friday, April 5, 2013 4:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:55 PM
Points: 29, Visits: 351
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 »

Add to briefcase «««1234

Permissions Expand / Collapse