The First Hour on a SQL Server You Have Never Seen

⚡Answer four questions, in order. What is this, what is it supposed to do, what is it short of, and what is unprotected. The order matters more than the speed. Edition decides what is even legal to use, so it comes before anything you might recommend, and nothing gets changed in the first hour.

Inheriting an instance is a normal part of the job. Somebody left, a company was acquired, a contract started, or a server nobody has looked at since it was built has suddenly become your problem. The temptation is to open the thing that was reported and start fixing. The better move is to find out what you are holding first, because almost every recommendation you might make depends on facts you do not have yet.


STEP1

Step 1: What Is It

Version, edition, patch level and uptime, in that order. Edition first because it decides what is available: several features people assume are present are Enterprise only, and several that used to be were opened up to Standard in 2016 SP1, so guessing from memory is unreliable in both directions.

SELECT SERVERPROPERTY('ProductVersion') AS version,
       SERVERPROPERTY('ProductUpdateLevel') AS cu_level,
       SERVERPROPERTY('Edition') AS edition,
       SERVERPROPERTY('IsClustered') AS is_clustered,
       SERVERPROPERTY('IsHadrEnabled') AS is_hadr,
       si.sqlserver_start_time AS last_started,
       DATEDIFF(DAY, si.sqlserver_start_time, SYSDATETIME()) AS uptime_days
FROM   sys.dm_os_sys_info AS si;

Read the patch level from cu_level. ProductLevel only knows RTM and service packs, and current versions ship cumulative updates instead, so a SQL Server 2025 instance on CU8 still reads RTM there; ProductUpdateLevel carries the CU. SQL Server Builds and Support Lifecycle turns the version into a support date, and Get Patch Level names the release and the KB for you.

Uptime belongs in this step rather than later, because it decides whether the performance counters you are about to read mean anything. sqlserver_start_time is the anchor: much of what the DMVs report covers only activity since the last startup, so an instance up for months is showing you history rather than today. Measuring waits over an interval is how you get today back, and Get OS and Hardware Info puts version, CPU, memory and uptime in one row.


STEP2

Step 2: What Is It Supposed To Do

Databases, recovery models, log state and owners. The recovery model is the one that catches people out: in FULL recovery the log keeps growing until a log backup runs, so a FULL database with no log backup job grows its log until the disk fills, and that combination is common on servers nobody owns. A database in SIMPLE that somebody assumes is point-in-time recoverable is the same problem wearing the opposite hat.

SELECT name,
       state_desc,
       recovery_model_desc,
       log_reuse_wait_desc,
       SUSER_SNAME(owner_sid) AS owner
FROM   sys.databases
ORDER  BY name;

log_reuse_wait_desc sits next to the recovery model because it shows the problem already happening: LOG_BACKUP means the log cannot be reused until a log backup runs, which What log_reuse_wait_desc Is Telling You walks through. Get Database Summary adds file sizes and backup currency per database, and Get Recovery Model Audit checks each recovery model against the backups the database actually has.

SSMS results of Get-DatabaseInventory on SQL Server 2025 showing state, recovery model and log_reuse_wait_desc per database, with DemoDatabase in FULL recovery waiting on LOG_BACKUP

The same check from Get Database Inventory: DemoDatabase is in FULL recovery with LOG_BACKUP as its log reuse wait, the log backup gap described above.


STEP3

Step 3: What Is It Short Of

Now the resource question, and only now, because you know how long the counters have been running. Memory configuration, MAXDOP, and whatever the server spends its time waiting on. Configuration that differs from the default is the interesting part: somebody changed it deliberately, usually for a reason nobody wrote down.

SELECT name, value, value_in_use, is_dynamic, is_advanced
FROM   sys.configurations
WHERE  value <> value_in_use
   OR  name IN ('max server memory (MB)', 'max degree of parallelism',
                'cost threshold for parallelism')
ORDER  BY name;

Read the three named settings first, then any other row. value and value_in_use differ when a change was made but RECONFIGURE never ran or the setting waits on a restart, which is a change queued for the next reboot. Some differences are expected: min server memory configured at 0 shows 16 in use.

sys.configurations has no default column, so this query cannot list every non-default setting on its own. Get Instance Configuration Score checks the settings that matter in one pass and returns PASS, WARN or FAIL per item. SQL Server MAXDOP and Cost Threshold covers what to set, and Get Memory Configuration and Usage and Get Wait Statistics answer the memory and waiting parts.


STEP4

Step 4: What Is Unprotected

The question that matters most and gets asked last, because it is the one where a wrong answer is unrecoverable rather than merely slow. Three things: when each database was last backed up, when it was last checked for corruption, and who has sysadmin.

A server can look healthy on every performance metric and still be one disk failure away from data loss. Backup age and CHECKDB age are the two numbers that tell you whether the last month of work exists anywhere else.

SELECT d.name,
       d.recovery_model_desc,
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log,
       DATABASEPROPERTYEX(d.name, 'LastGoodCheckDbTime') AS last_good_checkdb
FROM   sys.databases AS d
LEFT   JOIN msdb.dbo.backupset AS b
       ON b.database_name = d.name
WHERE  d.name <> 'tempdb'
GROUP  BY d.name, d.recovery_model_desc
ORDER  BY last_full;

A NULL last_full is a database with no backup history on this instance, and a last_good_checkdb of 1900-01-01 means no successful CHECKDB on record. Backup history is local to the instance, so confirm before raising it: backups taken on another availability group replica, or by a snapshot tool that bypasses SQL Server, leave no row in msdb.

Get Last Database Backup Times and Get Last DBCC CHECKDB are the fuller versions of those columns. Get Sysadmin Members answers the third question; a Windows group in that list gives sysadmin to every one of its members.


What Not To Do In The First Hour

  • Do not change anything. You do not yet know why it is set that way, and an unfamiliar server is the worst place to discover that a setting was load-bearing.
  • Do not run a repair. If you find corruption, the first move is to protect the backups you already have, not to reach for REPAIR_ALLOW_DATA_LOSS. DBCC CHECKDB Found Corruption covers what to do instead.
  • Do not restart it to “clear things down”. You lose every counter that would have told you what was happening, and you will need those.
  • Do not kill sessions because a list looks long. Long-running is not the same as stuck, and the head of a blocking chain is not always the session doing the most work. Troubleshoot SQL Server Blocking finds the real head.

The One-Command Version

Everything above is a list of questions rather than a list of scripts, on purpose: the order is the useful part and it is worth knowing whether or not you use any particular tool. If you want the same ground covered in one pass, Run a Full SQL Server Health Check collects it and hands back a findings list rather than raw output, which is a shorter route to the same answers.


Frequently Asked Questions

Why does edition come before performance?
Because it changes what you are allowed to recommend, which is why edition is question one. Suggesting a feature the instance is not licensed for wastes the credibility you need for the recommendations that matter, and edition is a two-second check.
What if the server has been up for a year?
Then the cumulative performance counters describe a year, not today, and you should either start a snapshot-and-delta collection or accept that the numbers are a profile rather than a diagnosis. Do not clear the counters on a server you have owned for an hour.
Is there anything worth changing on day one?
Rarely. The exception is something actively dangerous and reversible, such as a backup job that has been failing silently. Even then, fix the job rather than the schema. Get SQL Agent Job Failure Summary shows what has failed in the last 7 days.
How do I know which config values are non-default?
sys.configurations has no default column, and value against value_in_use only shows changes still waiting on RECONFIGURE or a restart, as in step 3. For the settings that matter, Get Instance Configuration Score checks each one and says PASS, WARN or FAIL. Treat every surprise as a question rather than a fault: somebody set it deliberately, and finding out why is part of taking the server on.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *