Tag: Troubleshooting
-
Troubleshooting Database Mirroring Issues in SQL Server
Database Mirroring is deprecated, but you will still see it in many production environments. It was officially marked deprecated in SQL Server 2012 and has remained in that state for well over a decade. In my own career as a DBA, it has been “deprecated” the entire time, yet still widely deployed and fully supported.…
Written by
-
Get Current Date & Time in SQL Server
SQL Server has 6 functions that return the current date and time. They all read the same clock, the operating system clock of the server, and differ in precision, in whether you get local time or UTC, and in whether the time zone comes with the value. ⚡For new code, use SYSDATETIME(), or SYSUTCDATETIME() for…
Written by
-
Grant VIEW SERVER STATE in SQL Server
🚨Part of the SQL Server Errors series, the exact messages and what actually causes them.Related pillar: Security Msg 300 · Level 14 · State 1SQL Server 2022 and later VIEW SERVER PERFORMANCE STATE permission was denied on object ‘server’, database ‘master’. Msg 297, Level 16, State 1 The user does not have permission to perform…
Written by
-
How to Get the SQL Server IP Address
The firewall team, or a vendor, wants the exact IP address and port before they will open an allow-list rule, and the server has more than one address. Do not read it off ipconfig and do not assume 1433: ask SQL Server which address and port the connection actually arrived on. ⚡On a TCP connection,…
Written by
-
SQL Server Error Severities Explained
🚨Part of the SQL Server Errors series, the exact messages and what actually causes them.Related pillar: Performance & Troubleshooting ⚡Read the Level before you read the message. 0 to 10 is informational, 11 to 16 means the statement is wrong and the server is fine, 17 to 19 is a resource limit or an engine…
Written by
-
How to Right-Size SQL Server Database Files
Auto-growth is not the problem. Unplanned, reactive growth is. When a file is undersized SQL Server extends it repeatedly under load, every extension is a pause, and if those pauses land in the peak hour they show up as latency, I/O pressure and the occasional application timeout. Right-sizing is about making growth rare, predictable and…
Written by
-
SQL Server: ALTER DATABASE SET ENABLE_BROKER Taking a Long Time (Fix)
After a recent Availability Group failover, Database Mail stopped sending emails. On investigation, the underlying cause was Service Broker not being enabled on msdb, the system database Database Mail runs out of. Enabling it should be instant. Instead the statement sat there, and this is what it was waiting on. To confirm the status, I…
Written by
-
SQL Server: Ad hoc Update to System Catalogs is Not Supported (Fix)
🚨Part of the SQL Server Errors series, the exact messages and what actually causes them.Related pillar: Server & Configuration Msg 5808 · Level 16 · State 1 Ad hoc update to system catalogs is not supported. ⚡Someone has set allow updates to 1, and until it goes back to 0 every RECONFIGURE on the instance…
Written by
-
How to Kill a SPID in SQL Server
⚡Find the head blocker, then KILL 69; with its session id. Whatever it had not committed rolls back, which can take longer than the work did and cannot be stopped. Watch it with KILL 69 WITH STATUSONLY;. In SQL Server, every connection to the database engine is assigned a Session Process ID, commonly known as…
Written by
-
How to Check Blocking SPIDs in SQL Server
Blocking is one of the most common causes of performance issues in SQL Server. When one session holds a lock on a resource and another session needs that same resource, the second session waits. If that wait persists, users experience slowness. Understanding how to quickly identify blocking SPIDs is a core DBA skill. This guide…
Written by
- Cannot Connect to WMI Provider: SQL Server Configuration Manager Will Not Open
- SQL Server Configuration Manager: Where to Find It and What a DBA Uses It For
- DBA Scripts: Get Failed Login Summary
- Query Was Fast Yesterday, Slow Today: Find What Changed
- The Current Transaction Cannot Be Committed (Error 3930)
- Sort and Hash Spills to TempDB: Find the Query and Fix the Grant
- Backups & Recovery (17)
- DBA Scripts (154)
- High Availability (HA) (13)
- Installation & Configuration (58)
- Maintenance (25)
- Migration & Upgrades (14)
- Monitoring (9)
- Performance Tuning (37)
- Security (Encryption & Permissions) (23)
- Storage & Capacity (17)
- T-SQL Fundamentals (15)
- Troubleshooting (102)
- Wait Types (235)