Drive Space Monitoring Gets An Update

Question

Post reply

Drive Space Monitoring Gets An Update

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

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

CelticCodingSolutions Newbie Points: 9 More actions · Answer 1

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

to

[' + @ServerName + '].master

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

arnipetursson SSCertifiable Points: 6557 More actions · Answer 2

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

}

CDA SSC Eights! Points: 848 More actions · Answer 3

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?

telcogod SSC Enthusiast Points: 143 More actions · Answer 4

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

markdh.climb SSC Enthusiast Points: 192 More actions · Answer 5

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)