Monitoring Disk Space and Sending Alerts with TSQL

  • 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)

  • 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

  • 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,

  • 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

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

  • Hi Bob very good answer.

    Could you please post the code of your working solution?

    Thanks

  • 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

  • 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

  • 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 39 (of 39 total)

You must be logged in to reply to this topic. Login to reply