DBA Scripts: Get Instance Configuration Snapshot

Part of the DBA-Tools Project.

---

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 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:

• Baselines 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/script-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. A handful of the more operationally relevant ones from this run:

min server memory (MB) is a genuine example of pending_restart = 1 here: configured to 0, but still running at 16, its previous value, until the next service restart.

---

Understanding the Results

• configured_value vs running_value — most settings apply immediately (RECONFIGURE), but some, particularly memory and CPU-related ones, require a service restart. When these two columns differ, that’s pending_restart = 1.
• pending_restart — a change was made but hasn’t taken effect. Worth checking before assuming a configuration change is live, especially after a change someone else made.
• minimum / maximum — the valid range for the setting. Useful context when deciding a new value.
• 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.

---

Related Scripts

You may also find these scripts useful:

• Server Inventory (hub)
• Instance Configuration Score
• MAXDOP Configuration
• Memory Configuration and Usage
• OS Configuration Checks
• Resource Governor Config
• SQL Server CPU Topology and Scheduler Details
• Trace Flags

---

Frequently Asked Questions

Why does a setting show a different configured and running value?

Because that setting requires a service restart to take effect. RECONFIGURE applies most changes immediately, but memory and CPU-related settings often need a restart before running_value catches up to configured_value.

How is this different from Get Instance Configuration Score?

This script is the raw snapshot, every setting, unfiltered. Instance Configuration Score (a separate post) is a graded audit that scores a subset of settings against best-practice recommendations. Use this one for a full baseline or drift comparison; use the score for a quick pass/fail read.

---

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.