Drive Space Monitoring Gets An Update

  • Shaun, love the code, but I found a small bug that seems to be related to what Eugene.Teh had.

    Our servers are named "COUNTRYCODE-SQL-BOX" e.g. GB-SQL-01 but the GatherServerDiskUsageData error'd:-

    Msg 102, Level 15, State 1, Line 13

    Incorrect syntax near '-'.

    Msg 102, Level 15, State 1, Line 13

    Incorrect syntax near '-'.

    So I tried adding the server as [GB-SQL-01] in the ServersToCheckDiskStats Table which worked for the SP but then failed for the DBDiskSpaceAnalysis.ps1 script.

    So I modified your SP GatherServerDiskUsageData e.g.:-

    ' + @ServerName + '.master


    [' + @ServerName + '].master

    and that worked a treat. Happy to send you an updated SP.

  • This is why I use WMI calls against physical server names:

    foreach($Volume in $(gwmi -computername $ServerName win32_volume | where-object{$_.DriveType -eq 3}))


    # First create a property bag to hold needed properties

    $objVolume = New-Object PSObject

    $objVolume | add-member NoteProperty Computer ""

    $objVolume | add-member NoteProperty VolumeName ""

    $objVolume | add-member NoteProperty Capacity_MB ""

    $objVolume | add-member NoteProperty FreeSpace_MB ""

    $objVolume | add-member NoteProperty BlockSize_KB ""

    # Populate instance of property bag

    $objVolume.Computer = $ServerName

    $objVolume.VolumeName = $Volume.Name

    $objVolume.Capacity_MB = [int]$($Volume.Capacity/1048576)

    $objVolume.FreeSpace_MB = [int]$($Volume.FreeSpace/1048576)

    $objVolume.BlockSize_KB = [int]$Volume.BlockSize/1024

    $VolArray = $VolArray + $objVolume


  • if it's wanted, I can add the mount point support piece; we use those heavily.

    there's multiple different methods to do this, depending on if your environments are older than 2k8r2 +SPx or not; which method is preferred?

  • I use wmic for mount point

    declare @table table (id int identity(1,1), textdata varchar(4000))

    insert @table

    exec xp_cmdshell 'wmic volume get capacity, drivetype, freespace, caption /format:csv'

    ; with cte as (

    select Data.value('(/root/r/text())[1]','varchar(1000)') Node

    , convert(bigint,Data.value('(/root/r/text())[2]','varchar(1000)')) / (1024 * 1024 * 1024) Capacity

    , Data.value('(/root/r/text())[3]','varchar(1000)') Caption

    , Data.value('(/root/r/text())[4]','varchar(1000)') DriveType

    , convert(bigint,replace(Data.value('(/root/r/text())[5]','varchar(1000)'),char(10),'')) / (1024 * 1024 * 1024) FreeSpace

    from @table

    cross apply (select convert(xml,'<root><r>' + replace(textdata,',','</r><r>') + '</r></root>') as Data) textdataXML

    where len(ltrim(rtrim(textdata))) > 1

    and id > 2

    and Data.value('(/root/r/text())[4]','varchar(1000)') = 3


    select Caption DriveName

    , Capacity

    , FreeSpace

    , convert(money,FreeSpace) / convert(money,Capacity) PercentFree

    from cte

    where Capacity > 0

    order by 3 desc

  • We had a requirement recently to monitor not just database size, but application folders, i.e. a folder structure containing a combination of sql database files, temp import files, templates, output documents etc.

    I know it sounds primitive but as a quick and dirty solution we just execute a DIR command, output the results into a text file, import it and then process the data into a summary table. Works like a treat, bit lazy though ;o)

Viewing 5 posts - 61 through 64 (of 64 total)

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