sql salt

T-SQL Tuesday #40

This month’s T-SQL Tuesday is hosted by Jen McCown, and the topic is filegroups.  I chose to write about one way to find out how many filegroups a particular database has, as well as if the PRIMARY filegroup is default.  There are a few reasons why you may want to do this check.  For instance, if filegroup restoring is part of your Disaster Recovery plan in order to get the database online as quickly as possible then there are a few basic necessities to ensure this is possible and streamlined.

One of the checks is to see if there are more than one filegroup.  After all, the power of a filegroup restore is to trickle the restores instead of waiting for the entire database to restore and bring the database online.

The second check is to see if the PRIMARY filegroup is the default filegroup.  One of the reasons why this may not be desirable is because when database objects are created without specifying a destination filegroup, they will be created in the default filegroup.  This may bloat the PRIMARY filegroup if it is the default filegroup (which it is…by default), and delay the database from being brought online with a filegroup restore.  More information regarding the default filegroup can be found on Books Online.

One way to get this information is through PowerShell and the utilization of SMO.  Below is the sample code to grab this configuration and display it to the host:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
    Out-Null

##### VARIABLES #####
$SqlServerName = "YourInstanceName"
$SqlDatabaseName = "YourDatabaseName"
### END VARIABLES ###

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

$DbFileGroups = $SqlServer.Databases[$SqlDatabaseName].FileGroups

# check to see if there is only one filegroup on the specified database
if ($DbFileGroups.Count -eq 1) {
    Write-Host "Warning!!!  $SqlDatabaseName contains only 1 filegroup" -ForegroundColor Red
}
else {
    Write-Host "$SqlDatabaseName contains $($DbFileGroups.Count) filegroups" -ForegroundColor Green
}

# check to see if the PRIMARY filegroup is default
if ($DbFileGroups | Where-Object {$_.Name -like "PRIMARY"} |
        Select-Object -ExpandProperty IsDefault) {
    Write-Host "Warning!!!  The PRIMARY filegroup is default" -ForegroundColor Red
}
else {
    Write-Host "The PRIMARY filegroup is not default" -ForegroundColor Green
}

Ultimately, there are just two conditional checks on the FileGroups property of the Database object.  There are two variables that will need to be modified:  $SqlServerName and $SqlDatabaseName.  They are respectively the instance name and the database name to run the check against.

This check is not intended for all databases across the board.  But if you expect a database to have multiple filegroups, and the PRIMARY filegroup not to be default then the above method is a quick way to find this using PowerShell.  If there are any comments, questions, or suggestions please feel free to leave a comment below or email me at sqlsalt@outlook.com.