Thank goodness for sys.dm_os_volume_stats!

When I sit down to look at a SQL Server system that I'm not familiar with, there are a lot of things I want to know about its layout and configuration.

Whenever anyone listens, I still recommend separating transaction logs and database files at the LUN level.  And for high traffic databases, only one file (whether database file or transaction log) per LUN.

You can get a lot of great details out of sys.master_files.  But, sys.master_files doesn't know which LUNs the files are on, which files are sharing LUNs, etc.

Since SQL Server LUNs may well be mounted volumes at subdirectory mount points, instead of mounted only at Windows drive letters, this can get tricky.  There might be nested mounted volumes along a given directory path, making it appear that database files and/or transaction logs are colocated on a LUN when they are not.

Thank goodness for sys.dm_os_volume_stats!!!

A quick call of the function with database_id and file_id, and a table is returned - including the volume mount point, the LUN total capacity, and the LUN freespace!

But... this function was introduced in SQL Server 2008 R2 SP1.  There are a lot of systems on versions without that function.

So I spent almost a whole day wrangling with powershell, figuring it would be a whole lot easier to pull info out of SQL Server sys.master_files into powershell and then use the logical device class to round out the information I want.

People that are good at powershell scripting will no doubt cringe at my lack of finesse.  That's ok.  I'm not a Win32_Unicorn.  No illusions of grace on my part.  This gets me what I would normally grab out of sys.master_files and sys.dm_os_volume_stats.  Gives a lot of context when you are staring at logical disk counters in perfmon, trying to figure out why disk IO is seemingly impossibly imbalanced :)

I'm clumsy at the powershell dance... and I'm not proud.  So if there are suggestions to make this code tighter, easier to read... all-around nicer I'm all ears.  I won't even mind too much if there's some good old mockery.  I know this code ain't pretty.  But it'll do for now.

$disks = gwmi Win32_Volume | where { $_.Name -notlike '\\*' } | Select  Name,Capacity,Freespace | Sort Name
$files = Invoke-Sqlcmd -Query "SELECT DB_NAME(database_id) [DB_NAME],Physical_Name,size,growth,is_percent_growth FROM sys.master_files;"
$fileDisks = @(0) * $files.Count
$capacity = @(0) * $files.Count
$freespace = @(0) * $files.Count
for ($jk = 0 ; $jk -lt $files.Count + 1 ; $jk++ )
   {If (!$files[$jk].Physical_Name) {continue}
    Else
      {$tempfile=$files[$jk].Physical_Name
       for ($ij = 0 ; $ij -lt $disks.Count + 1 ; $ij++ )
          {if (!$disks[$ij].Name) {continue}
           else
              {$tempdisk = [regex]::escape($disks[$ij].Name)
              if ( $tempfile -match $tempdisk )
                 {$fileDisks[$jk] = $disks[$ij].Name
                  $capacity[$jk] = $disks[$ij].Capacity/(1024*1024*1024)
                  $freespace[$jk] = $disks[$ij].Freespace/(1024*1024*1024)
                  }
               }
            }
        }
    }
write-host -nonewline "db_name,physical_name,size,growth,volume,capacity,freespace,is_percent_growth"
write-host ""
for ($jk = 0 ; $jk -lt $files.Count + 1 ; $jk++ )
  {If (!$files[$jk].Physical_Name) {continue}
   Else
      {write-host -nonewline $files[$jk].DB_NAME ","
       write-host -nonewline $files[$jk].Physical_Name ","
       write-host -nonewline $files[$jk].size ","
       write-host -nonewline $files[$jk].growth
       write-host -nonewline ","
       write-host -nonewline $fileDisks[$jk] ","
       write-host -nonewline $capacity[$jk]
       write-host -nonewline ","
       write-host -nonewline $freespace[$jk]
       write-host -nonewline "," $files[$jk].is_percent_growth
       write-host ""
       }
   }