Review Database File Properties with PowerShell

One of the things a database administrator needs to keep track of is the usage of the file systems where the database files are kept. One of the features of the Windows operating system is the incorporation of Windows Management Instrumentation (WMI) classes, which allow you to view the state of the operating system and related properties.

Related: Set Database Option Properties with PowerShell

The Win32_LogicalDisk class provides information about the drives a server uses, and DriveType 3 defines those drives mounted locally on a server, so the following PowerShell command will return the amount of space in bytes on the drive, and available.

$dspace = Get-WMIObject Win32_LogicalDisk -filter "DriveType=3" | Select-Object Name, Size, FreeSpace

It's easy to display the results by piping the $dspace variable to the Format-Table cmdlet, and you can use the -Autosize parameter to set the output based on the width of the data in the columns.

$dspace | Format-Table -Autosize

Now that we know how much space is on the drives, we can look at the database files, see where they are, and how much space each file is using. Before we do that, though, there are a couple of constructs we want to understand.

Understanding the Hash Table

The first is the "hash table." This, simply put, is a name-value pair, and provides a lot of flexibility. We signify a hash table by preceding a script block (a pair of curly braces - {}) by the @ character, and then enclosing the name-value pair within. So, this hash table

@{"Ohio" = "Columbus"}

Produces a name-value pair with "Ohio" as the name and "Columbus" as the value. We can use a hash table as a property in the property list of the Select-Object cmdlet to insert previously defined values into the results, so in our earlier example, we can add the server name to the object list by specifying a Name and Expression in a hash table, like this.

$svr = 'WS12SQL1'$dspace | Select-Object @{Name='Server'; Expression=@{$svr}},Name, Size, FreeSpace | Format-Table -Autosize

This adds the server name to the results, and when combining these results with those from other servers, allows it to make sense later.

Separating Actual File Name From Directory Path

One of the properties we'll be getting from SQL Server Management Objects (SMO) for each data file is the full path to the file, including the file name. Separating the actual file name from the directory path is useful so we can use the Split-Path cmdlet to do this. The -Leaf parameter to Split-Path returns just the filename, and the -Parent parameter returns the full directory without the filename.

Related: Gather SQL Server Instance Information with PowerShell

So we can combine the details of all the files into a single collection, we need to first create a variable that represents an empty collection, and we do that by preceding a set of parentheses by the @ character.

The SMO Database object has two collections in its list of properties that contain the file details. The log file(s)—hopefully, you don't have more than one—are in the LogFiles collection. The database files are separated by filegroup, so the FileGroups collection holds all the file groups, and the Files collection in each FileGroup object contain the actual database File objects. By iterating through these collections we get to the File objects, which contain the properties we want—specifically the logical file name (Name), the physical name with full path (FileName), the Size of the file in megabytes, and the UsedSpace, in kilobytes.

Get to Properties of Each Data File

To begin, we connect to the server, and create the empty collection, then we iterate through the server's Databases collection. For each database, we iterate through the FileGroups collection, then through the Files collection in each FileGroup, to get to the properties of each data file. We use Split-Path to get the directory and file names from the FileName property, then select the properties we want, using hash tables to return the database name, directory and file name properties. We then add that property set to the database files collection. We do the same thing through the LogFiles collection of the database.

When we're done, we just pipe the collection to the Format-Table cmdlet with the -Autosize parameter, and we have a list of all database files used for this instance of SQL Server in one table.

$svr = new-object ('Microsoft.SqlServer.Management.Smo.Server') localhost$dbfl = @()foreach ($db in $svr.Databases) {$dbname = $db.Nameforeach ($fg in $db.FileGroups) {foreach ($fl in $fg.Files) {$dirnm = $fl.FileName | Split-Path -Parent$filnm = $fl.FileName | Split-Path -Leaf$dfl = $fl | select @{Name="DBName"; Expression={$dbname}}, Name, @{Name="Directory"; Expression={$dirnm}}, @{Name="FileName"; Expression={$filnm}}, Size, UsedSpace$dbfl += $dfl}}    foreach ($fl in $db.LogFiles) {$dirnm = $fl.FileName | Split-Path -Parent$filnm = $fl.FileName | Split-Path -Leaf$dfl = $fl | select @{Name="DBName"; Expression={$dbname}}, Name, @{Name="Directory"; Expression={$dirnm}}, @{Name="FileName"; Expression={$filnm}}, Size, UsedSpace$dbfl += $dfl}}$dbfl | Format-Table -AutoSize

Here are the results.

By combining the results from this process with the results of the disk space query you can keep track of space usage at both the disk and database level.