T-SQL Tuesday #040: File and Filegroup Wisdom

tsql2sday

It’s time fore another TSQL Tuesday.  This was a hard theme for me to come up with a topic for.  Midnight DBA is hosting this month and the theme is File and Filegroup Wisdom.  The first thing I could think of I had already written about so that would be cheating.  One of the things I do mess around with a fair amount is Powershell so I thought it would be interesting to dig into how Powershell treats filegroups and files.  I’m only going to scratch the surface here but what I do cover will give you the base to explore on your own.

Pulling the groups

The first thing to do is to pull the data about the filegroups.  To start off with I’m just going to ouput all the data about the filegroups to the screen and format it in a way that’s a little easier to read:

[System.Reflection.Assembly ]::LoadWithPartialName("Microsoft.SqlServer.SMO" ) | Out-Null
 
$serverName = 'localhost'
 
$server = New-Object("Microsoft.SqlServer.Management.Smo.Server") $serverName
 
$db = $server.Databases | Where-Object {$_.name -eq 'AdventureWorks'}
 
$db.FileGroups | Format-Table

This will list all the file groups in the database and some of the basic information about them.  A couple of the fields, namely files and properties, can hold multiple pieces of information which can make them harder to read.  The name and some of the basic information such as whether or not the group is read-only.

Getting more properties

Most of the properties of the file group are visible here but if we dig into the Properties collection we do see one more.  We’re going to reference the first file group in the array, marked by spot 0, and list the properties the same way we listed the file groups above.

$db.FileGroups[0].Properties | Format-Table

The last property is PolicyHealthState so if you use policies to manage your system here is one place to get that info.

Digging into Files

Heading back up let’s take a look at the files in the group.  When working with collections like this Powershell makes it easy to iterate through each item to get information.  Here’s how we can view all the files in each file group:

$i = $db.FileGroups.Count
 
$db.FileGroups[0..$i].Files | Format-Table

The Parent property even has the name of the file group that it belongs to.  If you’re looking for the count of files in each file group we need to use a more traditional foreach loop.

foreach ($fg in $db.FileGroups) {
     $fileCount = $fg.Files.Count
     write-output "$fg $fileCount"
}

That’s all for now

That’s all I’m going to cover here but there’s a lot more to dig into.  For example, if you want more information on a particular file you can use the trick above to get more information on any of the files.  There are also a number of methods you can call on both filegroups and files to work with them.

3 comments

  1. T-SQL Tuesday #40 Roundup: Files and Filegroups…

    Good day all, and happy T-SQL Tuesday #40! Yes, this event has officially hit middle age now. I fully…

  2. SQL Server says:

    T-SQL Tuesday #40 Roundup: Files and Filegroups…

    Good day all, and happy T-SQL Tuesday #40! Yes, this event has officially hit middle age now. I fully…