Tag: SQL Server Administration

  • 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.…

  • Get Current Date & Time in SQL Server

    Getting the current date and time in SQL Server is straightforward. Choosing the correct function for your workload is what matters. Whether you’re stamping audit rows, logging ETL runs, or investigating production issues, SQL Server exposes several built-in functions with different precision and return types. All of these functions use the Windows OS clock of…

  • Grant VIEW SERVER STATE in SQL Server

    VIEW SERVER STATE is the permission that unlocks most server-level DMVs and DMFs. If someone can’t run basic instance health checks and keeps hitting permission errors, this is usually why. It gives visibility into what is happening on the box right now: active sessions, running requests, waits, connection details, and plan cache level query stats.…

  • How to Right-Size SQL Server Database Files

    Auto-growth is not the problem. Unplanned, reactive growth is. When database files are undersized, SQL Server is forced to extend them repeatedly under load. Each growth event introduces a pause. If those pauses occur during peak workload, they can cause noticeable latency, increased I/O pressure, and in some cases application timeouts. Right-sizing database files is…

  • 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 the system database required by Database Mail. To confirm the status, I ran: The is_broker_enabled column was returning 0, indicating that Service Broker was disabled for that database. I then attempted to…

  • SQL Server: Ad hoc Update to System Catalogs is Not Supported (Fix)

    While enabling show advanced options using sp_configure, I encountered an unexpected failure when executing RECONFIGURE: The change would not apply, and any further configuration attempts failed in the same way. This issue typically appears on systems where older configuration settings have been modified and never reset. In most cases, the root cause is the legacy…

  • How to Kill a SPID in SQL Server

    In SQL Server, every connection to the database engine is assigned a Session Process ID, commonly known as a SPID. There are situations where you may need to kill a SPID in SQL Server. This typically occurs when a session is blocking other queries, running indefinitely, holding locks during maintenance, or preventing a database from…

  • 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…

  • How to Check SQL Server Version

    Knowing exactly which SQL Server version and build is running is foundational DBA work. It comes up during patching, incident response, audits, upgrades, and when engaging Microsoft support. SQL Server exposes version information in several ways. Some are fast and visual, others are scriptable, and a few provide deeper installation detail when you need it.…

Blog Categories

Latest Posts

Blog Tags

Always On Availability Groups (4) Database Backups & Recovery (10) Database Maintenance (7) DBA Tools (29) Performance Troubleshooting (9) PowerShell (4) Query Behaviour (4) Schema Changes (1) SQL Agent (1) SQL Interview Questions (1) SQL Scripts (25) SQL Server Administration (9) SQL Server Configuration (7) SQL Server Errors (5) SQL Server High Availability (4) SQL Server Internals (4) SQL Server Management Studio (SSMS) (7) SQL Server Monitoring (10) SQL Server Networking (9) SQL Server Replication (1) SQL Server Security (8) SQL Server Versions (2) Storage and Capacity (14) T-SQL (9) Transaction Logs (8) Troubleshooting (14) Windows Server (4)