DBA Scripts: Get Instance Configuration Snapshot

Every sp_configure setting on an instance tells a small story: a value someone changed deliberately, a value nobody’s touched since install, or a value that’s been changed but is still waiting for RECONFIGURE or a restart to actually take effect. Without a full snapshot, most of that story is invisible, and configuration drift between servers that are supposed to be identical goes unnoticed until it causes a problem that’s hard to explain.

This script captures every sp_configure setting in a single result set, with a flag for anything that’s been changed but hasn’t taken effect yet, useful as a baseline, a change-tracking snapshot, or a config-drift comparison between servers.

Why Instance Configuration Snapshot Matters

Configuration settings quietly shape everything from query performance to security surface area, and most of them are set once (or never) and forgotten:

• Compare this snapshot against a future run to catch unauthorised or accidental configuration changes
• Surfaces settings that have been changed but not yet applied, since some sp_configure changes require a service restart
• Enables comparing configuration across servers that are supposed to be identical, a common source of “it works on the other server” bugs
• One query captures everything, rather than checking dozens of individual settings by hand

When to Run This Script

• Routine SQL Server health checks
• Before and after any configuration change, to document what actually changed
• Building a baseline for a new server, or before a migration
• Comparing configuration drift between servers in a cluster, AG, or environment tier

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-InstanceConfigurationSnapshot
Category    : configuration-and-environment
Purpose     : Capture all sp_configure settings for baseline review and change tracking.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-instance-configuration-snapshot/)
Requires    : VIEW SERVER STATE
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    name,
    value AS configured_value,
    value_in_use AS running_value,
    minimum,
    maximum,
    description,
    CASE WHEN value <> value_in_use THEN 1 ELSE 0 END AS pending_restart
FROM sys.configurations
ORDER BY name;

The script queries sys.configurations and returns every setting, its configured and running value, valid range, description, and whether it’s pending a restart to take effect.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Capture a full sp_configure snapshot:
.\run.ps1 Get-InstanceConfigurationSnapshot

# To run against a remote sql server:
.\run.ps1 Get-InstanceConfigurationSnapshot -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/monitoring/instance/Get-InstanceConfigurationSnapshot.sql
• powershell/wrappers/monitoring/instance/Get-InstanceConfigurationSnapshot.ps1

Example Output

The full result set returns every sp_configure setting, over 100 rows on a default installation.

min server memory (MB) is the row that shows pending_restart = 1 on this run: configured to 0, running at 16. That is not a change waiting for a restart. Microsoft documents a configured 0 showing as 16 in use on 64-bit as expected, and min server memory takes effect on RECONFIGURE without a restart anyway (MS Docs).

Understanding the Results

Most rows on a default installation are untouched defaults; the interesting ones are the handful someone has deliberately changed.

Best Practices

• Take a snapshot before and after any configuration change, so there’s a record of exactly what changed and when.
• Keep a baseline snapshot for servers that are supposed to be configured identically (AG replicas, load-balanced tiers), and diff future snapshots against it.
• Don’t assume a configuration change is live just because you ran RECONFIGURE. Check pending_restart for settings that need a service restart.

Microsoft’s reference covers sp_configure and sys.configurations in full.

Frequently Asked Questions

Summary

A full configuration snapshot is one of the simplest things to capture and one of the most useful things to have on hand when something doesn’t match expectations, whether that’s a query behaving differently on one server than another, or a setting someone swears they never changed.

Run this script as a baseline on every server, and again after any configuration change, so there’s always a record of exactly what the instance looked like at that point in time.

Related