SQL Server problems we investigate

An ERP, stock or finance system that used to respond quickly now takes seconds per screen, worst at month end. More server resources have been suggested.

Wait statistics against a baseline, the heaviest queries from query stats or Query Store, actual execution plans, blocking during busy periods and file I/O latency.

• A plan that changed after statistics or data volumes shifted
• Missing or unsuitable indexes for reports added since go-live
• Stale statistics, or maintenance that stopped running
• Max server memory or parallelism settings left at defaults

Targeted index and statistics changes, configuration corrections, query rewrites passed to the application supplier where the code is theirs, and restored maintenance.

Response times for the named slow screens and reports, before and after; duration of the heaviest queries; wait profile compared with the baseline.

A log file grows until a drive fills and the database stops accepting changes. It has been shrunk several times and keeps coming back.

Recovery model, log backup history, log_reuse_wait_desc in sys.databases, long-running or open transactions, replication or availability group state, VLF count and growth settings.

• FULL recovery with no log backups
• An open transaction or a stalled replication or AG secondary preventing log reuse
• Large index maintenance generating more log than backups clear

Log backups scheduled to the agreed RPO, the blocking cause removed, the log sized once to its real working size with sensible growth, and alerting on free space.

Log size stable over several business cycles, log reuse wait normally NOTHING or LOG_BACKUP, no repeat incidents, and restore to a point in time proven.

An auditor, insurer or customer asks for evidence of recovery. Backup jobs report success, but no one has restored a production database recently.

Backup history in msdb for every database, backup options and destinations, off-site copies, retention, and whether VM or appliance backups interact with SQL Server’s own.

• Databases added after the backup routine was set up and never included
• Backups stored only alongside the data
• Log chain broken by an ad hoc or third-party backup

Corrected backup coverage and schedule, test restores to a separate server followed by DBCC CHECKDB, a documented recovery procedure and a testing schedule.

Every database covered; restores completed and timed against the agreed RTO; recovery to a chosen point in time demonstrated; evidence the organisation can show.

Imports, integrations or maintenance fail intermittently. Staff find out in the morning when data is missing, and someone reruns the job by hand.

Job and step history, the SQL Server error log, deadlock and blocking information for the failure window, overlapping schedules, and permissions and credentials used by each step.

• Jobs overlapping with maintenance or backups and deadlocking
• Expired passwords or changed permissions on a proxy or linked server
• Growing data volumes pushing jobs past their window

Rescheduling and sequencing, fixes for the deadlocking or slow steps, corrected credentials, and failure notifications that reach someone who can act.

Job success rate and run durations over the following weeks, and no manual reruns.

A business system runs on a SQL Server version that no longer receives security updates. The upgrade has been deferred because of worries about what might break.

An inventory of databases, logins, jobs, linked servers and packages; deprecated and discontinued feature use; application vendor support statements; query performance under the new compatibility level.

• Uncertainty about application compatibility rather than a technical blocker
• Dependencies nobody has documented
• No test environment to try the upgrade in

A tested migration plan, a rehearsal on a copy, a cutover with verification and a rollback point, then compatibility level raised in a controlled step.

Users working normally on the new version after cutover, no regression in key query timings, and the instance on a supported, patched release.