Troubleshooting

1. General Strategy

Step 1 — Define the Problem

• Describe symptoms vs. expected behavior: "when I do … this happens … but I expect …"
• Facts only — no conclusions, no bias
• So not: "the server is down" or "the internet is down"
• Establish: since when, when did it last work?

Step 2 — Reproduce It

• Can you trigger the problem yourself? Every time?
• Ideally: reproduce it in a test environment — that way you can turn on logging and try fixes without risk
• Not reproducible: only incident logs are available → wait for more occurrences or add extra instrumentation

Step 3 — Gather Information

• Read the error message carefully
• Search the logs of every relevant subsystem: web server, database, syslog
• → Detective work

Step 4 — Find the Cause

Two strategies:

Strategy	Approach
Checklist	Blindly check the most frequent causes one by one
Bisecting	Systematically verify up to where it still works — requires knowledge of the chain (web server → application → network → db server)

Example: does the same query work directly on the database server? Then the problem isn't in the query.

Step 5 — Try a Solution

• Watch for side effects (e.g. a restart can cause other problems)
• Test whether the fix resolves the problem
• Doesn't work? Undo it — otherwise you make it worse

A workaround ≠ a fix

If the problem disappears after a restart, you still haven't found the real cause. You have a workaround, not a fix. Keep looking for the root cause.

---

2. Common Errors

Error	Check
Can't connect to SQL Server	Is the SQL Server service running? Network connectivity? Firewall?
Access denied	Are the login, user, password, permissions correct?
Too many connections	Number of active vs. max connections

Too Many Connections — Queries

-- View active processes
EXEC sp_who2;

-- Number of connections vs. maximum
SELECT @@CONNECTIONS, @@MAX_CONNECTIONS;

Via SSMS: Activity Monitor (right-click the server → Activity Monitor)

---

3. Tools

PowerShell — Checking and Restarting the Service

# Check status
Get-Service -Name mssqlserver

# Restart
Start-Service -Name MSSQLSERVER

T-SQL — Connections per Database

SELECT DB_NAME(dbid)    AS DBName,
       COUNT(dbid)      AS NumberOfConnections,
       loginame         AS LoginName
FROM   sys.sysprocesses
WHERE  dbid > 0
GROUP BY dbid, loginame;

Error Log

Via SSMS: Object Explorer → Management → SQL Server Logs → Current

Contains: startup timing info, configuration settings, error messages, warnings.

Dynamic Management Views (DMVs)

DMVs return server status information for: - Monitoring the status of a server instance - Diagnosing problems - Tuning performance

-- Overview of available DMVs
SELECT * FROM sys.system_objects WHERE type = 'V' AND name LIKE 'dm_%';

Reference: docs.microsoft.com — System Dynamic Management Views