April 17, 2008 at 4:54 am
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)
June 25, 2009 at 12:22 pm
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
October 16, 2009 at 1:42 pm
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,
December 8, 2010 at 11:01 am
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
November 22, 2011 at 8:23 pm
Bob, would you mind sharing how you check mount point free space with CHKDSK in Tsql ?
January 29, 2012 at 7:33 am
Hi Bob very good answer.
Could you please post the code of your working solution?
Thanks
February 28, 2012 at 1:59 pm
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
April 5, 2013 at 4:27 pm
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
May 13, 2016 at 12:35 pm
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!
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply