How to Troubleshoot SQL Server: Fix Errors Fast

This is the single most important habit for anyone who needs to troubleshoot SQL Server effectively. The ERRORLOG file is SQL Server's own diary, it records every startup, shutdown, login failure, corruption event, and configuration change. Most people skip it and go straight to Googling the error code. Don't do that.

You can open the current ERRORLOG directly in SSMS. Connect to the server, expand Management → SQL Server Logs, then double-click Current. Alternatively, find the file on disk, the default path for a default instance on SQL Server 2022 is:

C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG

For named instances, replace MSSQL16.MSSQLSERVER with MSSQL16.YOURINSTANCENAME.

You can also read the log via T-SQL, which is useful when SSMS won't connect:

EXEC xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, 'DESC';

Scroll to the bottom of the current log and work backwards. You're looking for lines marked Error or Warning. Pay close attention to the timestamp, did errors start immediately after a Windows Update, a backup job, or a specific time of day?

Common patterns you'll spot:

• Error: 18456, Severity: 14, State: 8, wrong password for SQL login
• Error: 18456, Severity: 14, State: 11, valid login but no access to server
• TDSSNIClient initialization failed, TCP/IP protocol issue at startup
• Error: 9001, transaction log unavailable, often disk full
• Error: 823 or 824, I/O error, potential disk corruption

Once you identify the error code and state number, you have exactly what you need to target the fix. The state number on a 18456 error alone narrows down the cause to a specific authentication failure reason, something the generic "Login failed" message never tells you.

Error 18456 is the most searched SQL Server troubleshoot query on the internet, and for good reason, it's vague to the point of uselessness without the state code. Here's the breakdown of the states you'll actually encounter in the wild:

• State 1: Generic failure, check ERRORLOG for the real state
• State 5: Invalid username (user doesn't exist)
• State 6: Attempt to use a Windows login with SQL authentication
• State 7: Login disabled and wrong password
• State 8: Correct login, wrong password
• State 9: Invalid password format
• State 11 or 12: Valid login but access to the server not granted
• State 38: Login valid but database doesn't exist or access denied

For State 8 (wrong password), reset the password in SSMS: expand Security → Logins, right-click the login → Properties → enter a new password → uncheck "Enforce password expiration" if this is a service account.

For State 11/12, the login exists but lacks server access. Run this in SSMS:

ALTER LOGIN [YourLoginName] ENABLE;
GO

If you're locked out entirely and need to recover sysadmin access, you'll need to restart SQL Server in single-user mode. Stop the SQL Server service, then start it from an elevated command prompt:

net start MSSQLSERVER /m"SQLCMD"

Then immediately connect with SQLCMD and add your Windows account as sysadmin:

sqlcmd -S . -Q "CREATE LOGIN [DOMAIN\YourUser] FROM WINDOWS; ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\YourUser];"

Restart the service normally afterward. If everything went correctly, you'll now be able to log in with full admin rights.

Also check authentication mode: In SSMS, right-click the server → Properties → Security. Make sure it's set to SQL Server and Windows Authentication mode if your application uses SQL logins. A server in Windows-only mode silently rejects all SQL logins with a 18456 error.

This one catches people constantly, especially after a Windows Server update, firewall rules can get reset or overridden by Group Policy. SQL Server listens on TCP port 1433 by default for the default instance. Named instances use dynamic ports by default, which is why the SQL Server Browser service (UDP port 1434) must be running for named-instance connections to work.

First, confirm what port your instance is actually listening on. Run this from an elevated PowerShell window on the SQL Server machine:

netstat -ano | findstr :1433

If you see a LISTENING entry, SQL Server is bound to that port. If nothing comes back, either the service isn't running or TCP/IP is disabled in Configuration Manager.

To add a Windows Firewall rule for SQL Server, open an elevated PowerShell session and run:

# Allow SQL Server default instance
New-NetFirewallRule -DisplayName "SQL Server Default Instance" `
  -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

# Allow SQL Server Browser (needed for named instances)
New-NetFirewallRule -DisplayName "SQL Server Browser" `
  -Direction Inbound -Protocol UDP -LocalPort 1434 -Action Allow

If you're in an environment managed by Group Policy, a locally-created firewall rule may get wiped on the next policy refresh. In that case, the rule needs to be created at the GPO level, work with your domain admin to add it under Computer Configuration → Windows Settings → Security Settings → Windows Defender Firewall with Advanced Security → Inbound Rules.

For named instances using dynamic ports, you have two options: either assign a static port (recommended for production) via SQL Server Configuration Manager → TCP/IP Properties → IP Addresses tab → set TCP Port under IPALL to 1433 or another fixed port, or ensure the SQL Server Browser service is running and clients are using the instance name format SERVER\INSTANCENAME rather than a port number directly.

After the firewall rule is in place, rerun the Test-NetConnection command from the quick fix section. A TcpTestSucceeded: True result means you're clear at the network layer.

When users complain that "SQL is slow" or applications are timing out even though the server appears healthy, blocking is almost always the culprit. A blocking chain happens when one session holds a lock on a resource and other sessions queue up waiting for it. If the blocking head has been sitting there long enough, you'll see cascading timeouts across your entire application.

The fastest way to spot blocking in SQL Server is this query, run it in SSMS while the problem is active:

SELECT 
    r.session_id,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time / 1000 AS wait_seconds,
    r.cpu_time,
    DB_NAME(r.database_id) AS database_name,
    SUBSTRING(st.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE r.statement_end_offset
        END - r.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

The blocking_session_id column tells you which session is the blocker. Note that SPID number. Now look at what that session is doing:

DBCC INPUTBUFFER(BLOCKING_SPID_HERE);

If it's a long-running report, an open transaction that forgot to commit, or a backup job locking system tables, you have your answer. In an emergency where the blocking head is clearly stuck, you can terminate it:

KILL 52; -- replace 52 with the actual blocking SPID

I know it feels drastic, but KILL on a blocking head is generally safe, SQL Server will roll back any open transaction cleanly. The sessions that were waiting will immediately resume. For ongoing blocking issues, look at whether your application is leaving transactions open, whether missing indexes are causing full table scans that hold locks longer than necessary, and whether you should enable Read Committed Snapshot Isolation (RCSI) on the database to reduce reader/writer conflicts. Enable it with:

ALTER DATABASE YourDatabase 
SET READ_COMMITTED_SNAPSHOT ON;

SQL Server error 9002, "The transaction log for database X is full due to LOG_BACKUP", is one of those problems that stops everything cold. When the transaction log fills up, SQL Server cannot write any new transactions. Inserts fail. Updates fail. The database effectively goes read-only. I know this is frustrating, especially when it hits production at the worst possible moment.

First, check the current log usage across all databases:

DBCC SQLPERF(LOGSPACE);

This returns each database's log file size and the percentage currently in use. Anything above 90% needs immediate attention.

Next, find out why the log isn't being reused. The log_reuse_wait_desc column in sys.databases tells you exactly what's holding the log space:

SELECT name, log_reuse_wait_desc, recovery_model_desc
FROM sys.databases;

Common values and their fixes:

• LOG_BACKUP, transaction log backups are not running. Take an immediate log backup: BACKUP LOG YourDB TO DISK = 'NUL'; (for testing, use a real path in production). Then schedule regular log backups.
• ACTIVE_TRANSACTION, an open transaction is preventing log truncation. Find and close it using the blocking query from Step 4.
• NOTHING, the log is ready to reuse, it just hasn't been shrunk. Run DBCC SHRINKFILE (YourDB_log, 1024); to reclaim space (replace 1024 with your target size in MB). Don't over-shrink, the log will just grow again immediately, causing performance problems.

If the underlying disk volume is genuinely full (not just the log file), check overall disk space from PowerShell:

Get-PSDrive -PSProvider FileSystem | Select-Object Name, Used, Free

If the data drive itself is full, you need to either add a disk, move files, or expand the volume before SQL Server can operate normally. As a very short-term measure only, you can set the database to the Simple recovery model temporarily, which truncates the log automatically at each checkpoint, but this eliminates your ability to do point-in-time recovery, so it's genuinely only for emergencies while you fix the disk situation.