When something breaks at 3 a.m., the first question is always what changed, and the honest answer is usually that nobody knows. Collecting server facts into one table every night turns that shrug into a query.
Give Each Server Facts Snapshot a Date
A server inventory that stores only the latest value loses its best feature: history. When a setting changes on Tuesday, you want Monday’s row. Create a small table in a DBA database. Give each snapshot a capture time and a stable instance key. Keep the version and a few settings in the same capture so you can correlate them later.
I have lost count of incidents where the setting behind the trouble changed weeks earlier. Without yesterday’s snapshot you are guessing. With it you are comparing.
I avoid one giant column full of prose. Typed columns are easier to filter and compare. The instance key should represent the server you intend to track, not just whatever label a client used to connect. A listener, DNS alias, and physical instance are different things. Record both the connection target and the physical instance where that helps.
Decide how long to retain rows. Daily snapshots are compact, but database file details grow with the number of databases. Set a retention policy that matches your audit and incident needs. Archiving old rows is easier than reconstructing a missing month.
Capture Version and Configuration
Start with a reliable version snapshot. SERVERPROPERTY returns the product version, edition, and update labels. sys.configurations exposes server settings, including both configured and currently running values. Those values differ after some changes until a restart or RECONFIGURE takes effect. Recording only value misses a useful warning.
A job that gathers Server Facts should fail visibly. Wrap its work in a transaction if all rows represent one snapshot. Give the run an identifier so every detail row can be tied to the same capture. If one query fails, log the error and alert the DBA instead of publishing a half empty snapshot as success.
Before you automate across servers, test the collection query locally on each version you support. A property missing on an older release can return NULL. Your table must allow that without masking a connection failure. NULL from a property and no response from a server mean different things.
SELECT
SYSDATETIMEOFFSET() AS CapturedAt,
@@SERVERNAME AS RegisteredName,
SERVERPROPERTY('MachineName') AS MachineName,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductUpdateLevel') AS UpdateLevel;Record Server Facts That Explain Incidents
Collect every setting if you want a full configuration history, or start with a focused set. max server memory, cost threshold for parallelism, and max degree of parallelism are common investigation points. Keep the setting name, configured value, and running value. Never store a screenshot as the only record. Text and numbers can be searched.
A daily comparison lets you ask who changed a value and when. The snapshot does not identify the person who made the change. It narrows the interval. Pair it with an approved change log or an audit if attribution matters. This distinction is important when the on call DBA gets a question before coffee.
Collecting settings also gives you a review queue. A change from one day to the next is a prompt to investigate, not automatically a defect. Maintenance windows, workload moves, and planned tuning all create legitimate differences. Record the reason beside the change once it is confirmed.
SELECT
name,
value AS ConfiguredValue,
value_in_use AS RunningValue
FROM sys.configurations
WHERE name IN
('max server memory (MB)',
'cost threshold for parallelism',
'max degree of parallelism')
ORDER BY name;
Add Databases and Sizes
A useful nightly row records each database name, state, compatibility level, and total file size. Use sys.master_files to sum allocated file pages. Convert pages to megabytes with the documented page size. Treat the result as allocated file size, not used data. A large log file can be mostly empty. Label the column honestly.
Database names alone are fragile identifiers. A database can be renamed, restored over another database, or moved between instances. Keep database_id for the snapshot, but do not treat that number as a permanent global identity. A separate owner or application mapping makes long term reports more useful.
When a database disappears from the next snapshot, check the job result first. Then investigate whether it was dropped, moved, or simply inaccessible to the collector. A clean history should distinguish a missing server from a server with no user databases.
SELECT
d.name,
d.state_desc,
d.compatibility_level,
SUM(CONVERT(bigint, mf.size)) * 8.0 / 1024 AS AllocatedMB
FROM sys.databases AS d
JOIN sys.master_files AS mf
ON mf.database_id = d.database_id
WHERE d.database_id > 4
GROUP BY d.name, d.state_desc, d.compatibility_level
ORDER BY d.name;Run the Server Facts Collection as a Job
Use a SQL Server Agent job on each instance for a local collector, or run a central job that connects to approved targets. A local job keeps working during a central network interruption. A central job simplifies reporting. Neither design excuses missing alerts. Pick the one your operations team can support.
Schedule the job away from backup and index maintenance where practical. The queries above are lightweight, but a fleet wide collector can create a burst of connections. Stagger jobs across instances. Use a dedicated account with only the permissions needed to read metadata and write to the inventory destination. Do not make the collector a sysadmin just to avoid permission work.
If you send results to a central database, protect the connection and document the endpoint. Do not embed a password in a job step. Use the Windows service identity or another approved credential method. A fact collection job is part of operations, so it deserves the same care as a backup job.
Report Changes, Not Just Rows
A table full of nightly rows becomes useful when you compare days. Look for changed ProductVersion values, settings where RunningValue changed, databases added or removed, and file growth that needs review. Compare the same instance key across dates. Do not join on display names that change after a rename.
Nobody reads a table of a thousand identical rows. Report only what moved since last night and people will actually open the email. Would you read the other kind?
A report of missing captures matters as much as a report of changed values. If a server reported every night and then stops, your inventory has lost sight of it. Check the job, network path, credentials, and server status. Do not silently carry yesterday’s facts forward and call them current.
Send a short daily summary to the DBA team. List only exceptions and the last successful capture time. A full export is available when someone needs details. This keeps the signal useful. It also makes the history table a practical operations tool instead of a shelf of forgotten snapshots.
Make the Snapshot Trustworthy
Validate the first run by comparing its rows with direct queries on a few instances. Check a named instance, a server with multiple databases, and a server that has a recent update. Inspect the Agent job history after the scheduled run. The test should prove that a failed target appears as failed, not as a blank but successful capture.
Add the inventory schema to your normal backup plan. Store enough information to recreate the Agent job and its permissions. A DBA database is still production data when the team depends on it. Test a restore if you plan to use this history during an incident.
I want the nightly server facts to answer simple questions quickly. Which build was running before the outage? When did max server memory change? Which database grew? If the table answers those questions without opening several remote sessions, the design has done its job.
Related reading on this blog: How to Check Your SQL Server Version, Edition and Patch Level and Server Settings Worth Checking on Any New Instance.
A table of server facts is not paperwork, it is the memory your servers cannot keep for themselves.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
