I get many questions about whether something is possible in PowerShell. One the 5 most popular questions I have received over the last 6 months is, How do I find out how much free space is available in each of file of my database?PowerShell lets you do this a few different ways. This article will explain the best way that I’ve found so far for accomplishing this task.

If you’re new to PowerShell then I suggest that you check out my quick-start checklist and the series I did for SQL University before getting started here. Then you’ll be ready to follow along with the code examples. In addition to those steps this basic information is helpful for new PowerShell users:

• $This is a variable in PowerShell. Variables don’t need to be declared.
• We’re going to be doing some “piping”. If you’re coming to PowerShell with a SQL Server background then think of pipes this way. Every time that you see the “|” operator know that once it’s done all the work on the left side of the pipe it will do a ‘SELECT INTO’ the next command on your script.
• We need to connect to the SQL instance you want to talk to. In our examples the variable $SMOserver in the is essentially the PowerShell code equivalent of a database instance in Object Explorer:

We have to crawl before we can walk so hang in there with us and stick it out to the end where it will all make perfect sense.

The Hard Way

If you sniffed too much C# code on your lunch break then this is likely how you’d approach the task of finding available file space inside your db with basic PowerShell:

$dbname="AdventureWorks"            
$Server="WIN7W510\R2"            
            
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null            
[System.reflection.assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null            
$SMOserver = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) -argumentlist $Server            
$db = $SMOserver.databases[$dbname]            
            
$cmdStatement="
$dbname"            
$cmdStatement+=foreach ($DFileGroups in $db.FileGroups)             
{            
foreach ($FGDFile in $DFileGroups.Files) {"
", $FGDFile.Name, $FGDFile.Size, $FGDFile.UsedSpace, $FGDFile.AvailableSpace, $FGDFile.FileName }            
}            
$cmdStatement+="
 "            
$cmdStatement+=foreach ($logFile in $db.LogFiles) {$logFile.Name, $logFile.Size, $logFile.UsedSpace, $logFile.AvailableSpace, $logFile.FileName }            
$cmdStatement            

This will output this information:

Seems pretty easy right? Unfortunately it doesn’t solve our entire problem. It has flashed nicely formatted results to our screen, but they still aren’t in our database. The obvious answer looks like a simple INSERT statement. But the results in front of you are note a data table. They are actually an array list. Luckily our solution is almost as simple as an INSERT statement.

Fixing the Array-Based Results that you get in PowerShell

PowerShell is great, but like anything in computers, you get precisely what you ask for. When I first tried to save multiple rows of data I didn’t realize what I needed to ask for. I just knew that the results that I was getting back weren’t what I wanted. What tends to happen is the cmdlet or code that you’re working with returns the data back to you as an array. In a lot of cases you can’t even spot this difference with your eyeball – as you noticed in the last example – so you think you have the correct result and then hit the same wall I did. (Unless you’re a .Net expert in which case you saw this coming a mile away.)

As it turns out I was not the first person to have this experience in PowerShell. I’ve posted about some that adventure on the Hey Scripting Guy Blog at TechNet if you want to read more details. Here we’ll focus on how overcoming this limitation. The short answer is go download Chad Miller’s ( blog | twitter ) Out-DataTable and Write-DataTable scripts. You want to save these scripts on your local machine and load them into your session by opening it in the ISE and hitting the F5 key. I keep these particular scripts in my profile so that they’re always there ready to go whenever I may need them since I use them for many PowerShell endeavors.

Let’s break down our script. We are going to pass in the instance name and database name of a database that we are interested in. Then we want to find out how much free space the data files have so we’re going to use the Get-SQLDataFile function that comes with the SQLServer Module of SQLPSX. After the Get-SQLDataFile command is finished gathering data we’re looking for it’s going to stuff that data into an $ArrayResults variable. Then we get to have fun with data types.

Get-SqlDatabase "WIN7W510\R2" "AdventureWorks" | Get-SqlDataFile | Format-Table dbname, FileGroup, FileName, Size, UsedSpace, FreeSpace;            
Get-SqlDatabase "WIN7W510\R2" "AdventureWorks" | Get-SqlLogFile | Format-Table dbname, FileGroup, FileName, Size, UsedSpace, FreeSpace;            

When we get the results back the thing that trips us up is that it’s a single array. Even if we have two data files (like I do), it’s still just one array. That just doesn’t even make sense to me. I think in rows and if you have two rows they should be two arrays right?

Apparently Not. To combat this unfortunate reality we will fight back with the Out-DataTable function. You’ll see that we’re using the same code below as we did above except that we changed the name of the variable to $DataTableResults and we added the “| Out-DataTable” portion.

$ArrayResults = Get-SqlDatabase "WIN7W510\R2" "AdventureWorks" | Get-SqlDataFile |            
Select dbname, FileGroup, FileName, Size, UsedSpace, FreeSpace;             
            
$ArrayResults.GetType()            

Now when we run the .GetType() we see that are variable has become a DataTable not an array. In my humble opinion this is one of the most important things you will ever learn about PowerShell. Not because it contains the words “Data” and “Table” but because it will change the way you think of PowerShell. Being able to work with datatables instead of arrays makes PowerShell exponentially more useful. Let’s look at how.

$DBinQuestion = Get-SqlDatabase "WIN7W510\R2" "AdventureWorks"             
            
$DBinQuestion | Get-SqlDataFile | Format-Table dbname, FileGroup, FileName, Size, UsedSpace, FreeSpace;            
$DBinQuestion | Get-SqlLogFile  | Format-Table dbname, FileGroup, FileName, Size, UsedSpace, FreeSpace;            

Now while the result that we received is exactly what we were looking for it wasn’t exactly the world’s nicest output. It’s more like a print-out than usable information. When we run the next script (below) the information coming back might* look the same but it isn’t. It’s going to be in a datatable and we will finally be able to put it to use. Looking at the results below they look pretty much the same as the results from above but they aren’t! The above statement gave us a result set not unlike the manner that PRINT gives us a result set. The code below is much more akin to the results of a SELECT statement. How does that help us?... almost there!

$DBinQuestion = Get-SqlDatabase "WIN7W510\R2" "AdventureWorks"             
            
$DBinQuestion | Get-SqlDataFile | Select dbname, FileGroup, FileName, Size, UsedSpace, FreeSpace | Out-DataTable            
$DBinQuestion | Get-SqlLogFile  | Select dbname, FileGroup, FileName, Size, UsedSpace, FreeSpace | Out-DataTable            

Putting all of this in Motion

Having information right now is a good thing but having information historically is even better. For that reason we’re going to create a table to store our results. I’ve included a basic table for you but I’ll let you pick how you want to build the primary key. We will start with the same steps as above except that we’re going to we’re going to start out by grabbing a list of our databases. Next we will loop through the databases one at a time and collect the information about every data file that they are composed of. After we’ve collected that information we are going to use the Write-DataTable function to insert the rows into a table before moving on to collect information from the next db.

 CREATE TABLE [dbo].[DatabaseFileFreeSpace](
      [Server] [varchar](128)NULL,
      [dbname] [varchar](128)NULL,
      [FileGroup] [varchar](128)NULL,
      [FileName] [varchar](128)NULL,
      [Size] DECIMALNULL,
      [UsedSpace] DECIMALNULL,
      [FreeSpace] DECIMALNULL,
      [CheckedOn] DATETIME NULL
 ) ON [PRIMARY]
 GO
 

And here is our PowerShell code:

$Instance = "WIN7W510\R2"            
            
foreach ($db in Get-SqlDatabase $Instance | Select Name)            
{            
$Results=Get-SqlDatabase $Instance $db.name | Get-SqlDataFile |            
Select Server, dbname, FileGroup, FileName, Size, UsedSpace, FreeSpace, timestamp | Out-DataTable;            
            
$Results+=Get-SqlDatabase $Instance $db.name | Get-SqlLogFile |            
Select Server, dbname, FileGroup, FileName, Size, UsedSpace, FreeSpace, timestamp | Out-DataTable;            
            
Write-DataTable -ServerInstance $Instance -Database CentralInfo -TableName DatabaseFileFreeSpace -Data $Results;            
}            

Hopefully this has at least given you some easy to remember PowerShell code as well as possibly saved you some time and frustration in implementing it.