Three Scripts for a First Look at Any Server

A new SQL Server is handed to you with almost no context. A first look at any server should establish configuration, recovery coverage, and live activity before changing a setting.

Begin a First Look at Any Server With Read-Only Questions

The first pass should not change settings. It should answer what instance this is, which databases it holds, whether recovery evidence exists, and what is running now. Those facts guide the next investigation. A script that immediately clears cache or changes max memory is not a first look.

I run the same three checks on every unfamiliar instance and save the output with collection time. That creates a baseline for later questions. The queries do not prove the server is healthy, but they keep assumptions from taking over the conversation.

Ask which application and owner depend on the instance. SQL Server can show database names, not the business priority behind them. Pair query output with an ownership inventory before scheduling work.

SELECT @@SERVERNAME AS InstanceName,
       SERVERPROPERTY('ProductVersion') AS ProductVersion,
       SERVERPROPERTY('Edition') AS Edition;

Script One: Configuration

sys.configurations shows server-level options, including configured and running values. Read max server memory, max degree of parallelism, cost threshold, and other settings relevant to the workload. Do not label a value wrong without knowing host memory, CPU layout, and application behavior.

I compare value and value_in_use. A configured change can be pending or dynamic. Record both. Look at the SQL Server service version and edition before relying on a feature. A setting name alone does not explain why it was chosen.

The query below is deliberately broad so the first pass can be saved. Filter to a small review set afterward. The first output is evidence, not a tuning recommendation.

SELECT name, value, value_in_use, is_dynamic
FROM sys.configurations
ORDER BY name;

Script Two: Databases and Backups

Join sys.databases to backup history with care. msdb history can be cleared or moved, and a file listed there can be missing. A last full backup date is useful evidence but not a restore test. Include recovery model and state so the backup schedule has context.

I look for databases with no expected backup row, not only the ones with a recent backup. A LEFT JOIN preserves them. Then I review differential and log backup history where the recovery plan requires it. Never assume a full backup alone meets a point-in-time target.

A database in an unusual state needs a separate follow-up. The first script reports state without attempting repair. Recovery questions belong to the owner and runbook.

SELECT d.name, d.state_desc, d.recovery_model_desc,
       MAX(b.backup_finish_date) AS LastFullBackup
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b
  ON b.database_name = d.name
 AND b.type = 'D'
GROUP BY d.name, d.state_desc, d.recovery_model_desc
ORDER BY d.name;

Script Three: Current Activity

sys.dm_exec_requests shows active requests, waits, blocking session IDs, and database context. It is a snapshot. A quiet result does not prove that the overnight batch is healthy. Capture current activity when the server is under the behavior you need to diagnose.

I filter out my own session and look for waits and blockers, then gather statement text under the permissions available. Do not assume every wait means trouble. Some waits reflect normal idle or asynchronous work. Compare with duration and the workload’s expected pattern.

The script uses documented columns from this exact DMV. Check permissions before relying on a blank result. Metadata visibility varies by server version and role.

SELECT session_id, status, command, database_id,
       wait_type, blocking_session_id, cpu_time, total_elapsed_time
FROM sys.dm_exec_requests
WHERE session_id <> @@SPID
ORDER BY total_elapsed_time DESC;

Interpret the Three Checks Together for a First Look at Any Server

Configuration, recovery, and activity can point to the same issue or different ones. High memory use by SQL Server is normal under many workloads. An old backup entry is concerning only relative to the recovery policy. A blocked request needs a blocking chain, not an automatic server restart.

I write a short fact list after running the scripts: instance identity, database count and states, backup gaps, and active concerns. Mark unknowns explicitly. A first look at any server should distinguish evidence from guesses. It should not become a surprise tuning session.

If the application reports slowness while the snapshot looks quiet, capture the workload during the problem or use Query Store and monitoring history. Timing matters. Three scripts are a start, not a continuous monitor.

Follow Up on Backup Evidence

Check backup job status, storage location, file availability, and restore testing. msdb history can show a successful backup that is no longer available. A test restore to another database is the practical proof that the chain works.

I compare the backup schedule with recovery point and recovery time targets. A full backup every night can still leave an unacceptable data loss window if log backups are required and missing. The first query surfaces the conversation. The restore plan completes it.

Keep encryption certificates or keys needed for encrypted backups in the recovery inventory. A valid backup file without its required key cannot be restored as intended. That is a recovery finding, not a catalog query problem.

Leave an Actionable Baseline After a First Look at Any Server

Save the output with instance name, collection time, permissions used, and any inaccessible databases. A partial inventory should be labeled partial. The next DBA can compare the same queries later and know which differences matter.

I do not treat the script output as a health score. It is a map for the next hour of work. Assign owners to follow-ups such as missing backup evidence, unknown databases, or a long-running request.

A first look at any server is successful when it prevents premature fixes. Read the configuration, recovery record, and current workload, then choose the next check from evidence. Three small scripts can make a new server much less mysterious.

A first look should leave a short, repeatable record, not a stack of screenshots. Save the server identity, configuration snapshot, database list, backup recency, and current activity with a capture time. Which finding needs immediate follow-up? Separate facts from judgments so another DBA can review the same evidence. I check backup history against actual retained files before calling a database protected.

Do not run expensive diagnostics indiscriminately on an unfamiliar production server. Start with catalog and DMV reads, note permission limits, and collect plan or workload details only for a concrete question. A quiet current-activity snapshot cannot prove the server is always quiet. It tells you what was visible at that moment.

Related reading on this blog: Finding Last Backup Time for All Database: Last Full, Differential and Log Backup and SQL SERVER 2019: New Values in Sys.Configurations.

A first look is not a tuning session, it is a reliable starting map of the server.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.