dbatoolsGet-DbaSpConfigure
View SourceSynopsis
Retrieves SQL Server sp_configure settings with default value comparisons for configuration auditing
Description
Retrieves all SQL Server instance-level configuration settings accessible through sp_configure, using SMO to gather comprehensive details about each setting. This function compares current configured and running values against SQL Server defaults to quickly identify which settings have been customized from their out-of-box values.
Essential for configuration auditing, compliance checks, and ensuring consistency across multiple SQL Server environments. The output includes advanced and basic settings, minimum/maximum allowed values, whether settings are dynamic (require restart), and flags non-default configurations for review.
Particularly useful when documenting server configurations, troubleshooting performance issues related to memory or parallelism settings, or preparing for server migrations where you need to replicate custom configurations.
Syntax
Get-DbaSpConfigure
[-SqlInstance] <DbaInstanceParameter[]>
[[-SqlCredential] <PSCredential>]
[[-Name] <String[]>]
[[-ExcludeName] <String[]>]
[-EnableException]
[<CommonParameters>]
Examples
Example: 1
PS C:\> Get-DbaSpConfigure -SqlInstance localhost
Returns all system configuration information on the localhost.
Example: 2
PS C:\> 'localhost','localhost\namedinstance' | Get-DbaSpConfigure
Returns system configuration information on multiple instances piped into the function
Example: 3
PS C:\> Get-DbaSpConfigure -SqlInstance sql2012 -Name 'max server memory (MB)'
Returns only the system configuration for MaxServerMemory on sql2012.
Example: 4
PS C:\> Get-DbaSpConfigure -SqlInstance sql2012 -ExcludeName 'max server memory (MB)', RemoteAccess | Out-GridView
Returns system configuration information on sql2012 but excludes for max server memory (MB) and remote access. Values returned in grid view
Example: 5
PS C:\> $cred = Get-Credential SqlCredential
PS C:\> 'sql2012' | Get-DbaSpConfigure -SqlCredential $cred -Name RemoteAccess, 'max server memory (MB)' -ExcludeName 'remote access' | Out-GridView
Returns system configuration information on sql2012 using SQL Server Authentication. Only MaxServerMemory is returned as RemoteAccess was also excluded.
Required Parameters
-SqlInstance
The target SQL Server instance or instances. This can be a collection and receive pipeline input
| Property | Value |
|---|---|
| Alias | |
| Required | True |
| Pipeline | true (ByValue) |
| Default Value |
Optional Parameters
-SqlCredential
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
-Name
Return only specific configuration settings instead of all sp_configure values. Accepts either display names from sp_configure (‘max server memory (MB)’) or SMO property names (‘MaxServerMemory’).
Use this when you need to check specific settings like memory configuration, parallelism, or security options without retrieving the full list.
| Property | Value |
|---|---|
| Alias | Config,ConfigName |
| Required | False |
| Pipeline | false |
| Default Value |
-ExcludeName
Exclude specific configuration settings from the results. Accepts either display names from sp_configure or SMO property names.
Useful when generating reports or comparisons where you want to hide standard settings and focus on custom configurations.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
-EnableException
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Outputs
PSCustomObject
Returns one object per sp_configure setting, providing comprehensive configuration details and comparison against SQL Server defaults.
Default display properties (via Select-DefaultView):
- ComputerName: The computer name of the SQL Server instance
- InstanceName: The SQL Server instance name
- SqlInstance: The full SQL Server instance name (computer\instance format)
- Name: The SMO property name of the configuration setting (e.g., MaxServerMemory, CostThresholdForParallelism)
- DisplayName: The sp_configure display name of the setting (e.g., “max server memory (MB)”)
- Description: Human-readable description of what the configuration setting controls
- IsAdvanced: Boolean indicating if this is an advanced configuration setting (requires ShowAdvancedOptions enabled)
- IsDynamic: Boolean indicating if the setting takes effect immediately (true) or requires restart (false)
- MinValue: The minimum allowed value for this setting (int/numeric)
- MaxValue: The maximum allowed value for this setting (int/numeric)
- ConfiguredValue: The current configured value stored in sys.configurations (may require restart to take effect)
- RunningValue: The currently running/active value in memory on the SQL Server instance
- DefaultValue: The out-of-the-box default value for this setting from SQL Server
- IsRunningDefaultValue: Boolean indicating if the running value matches the default value (true = using default, false = customized)
*Hidden properties (accessible with Select-Object ):
- ServerName: The server name (same as SqlInstance)
- Parent: Reference to the SMO Server object
- ConfigName: Alias for Name property
- Property: Reference to the underlying SMO ConfigurationProperty object