SQL DBA Blog: Home

  • Script: Check xp_cmdshell, CLR & Database Mail Configuration

    This script checks whether xp_cmdshell, CLR integration (including CLR strict security), and Database Mail are enabled on the instance. These features expand SQL Server’s capabilities beyond pure database operations. While commonly used, they increase surface area and should be intentionally configured. This script is useful during security reviews, environment validation, or when inheriting a server.…

  • Database Cannot Be Opened – It Is in the Middle of a Restore

    You may see the following error when attempting to access a database during or after a restore operation: Database “[DatabaseName]” cannot be opened. It is in the middle of a restore. This message is easy to misinterpret, especially during incidents or maintenance windows.It does not indicate corruption or failure by itself. This error means the…

  • Script: Check AG Replica Role and Synchronization State

    This script returns the current role, synchronization state, health status, and failover configuration for replicas participating in an Always On Availability Group. It is commonly used during failover validation, replica troubleshooting, and general high availability health checks. The Script Example Output The result set shows one row per replica in each Availability Group. Important columns…

  • Kill All User Sessions on a Database in SQL Server

    When you need to drop, restore, or change database settings, active user sessions often get in the way. SQL Server will block these operations until all connections are cleared. In busy environments, waiting is rarely practical. This post shows a safe, deliberate, and review-first way to identify and kill all user sessions connected to a…

  • Script: Check Always On Availability Group Latency

    This script returns replication latency information for databases participating in an Always On Availability Group (AG). It shows how far each secondary database is behind the primary, the redo queue size, redo rate, and an estimated recovery completion time. It helps identify AG latency, redo backlog, and data movement delays between primary and secondary replicas.…

  • Estimate Backup and Restore Completion Time in SQL Server

    When a database backup or restore is running, one of the first questions is simple: How long does it have left? SQL Server exposes estimated completion information for active backup and restore operations. You can query it directly to see progress, elapsed time, and an approximate finish time without guessing or relying on external tooling.…

  • Script: Transaction Log Size and Usage by Database

    This script returns transaction log size and usage information for all databases on the instance. It shows total log size, used log space, percentage used, and recovery model. This is commonly used during log growth incidents, backup troubleshooting, or when validating log management across an environment. The Script Example Output The result set shows one…

  • Get Last Database Backup Times in SQL Server

    Checking the last backup times is one of the first things to do when reviewing a SQL Server instance. Before making changes, running maintenance, or troubleshooting issues, you need to know whether reliable recovery points actually exist. This post shows a fast, reliable way to check the most recent full, differential, and transaction log backups…

  • Script: Check Disk Space on SQL Server

    This script returns disk space information for volumes that host SQL Server database data or log files. It shows total size, available free space, and percentage free space per drive or mount point. This is commonly used during storage pressure incidents, failed backups, restore troubleshooting, or proactive capacity reviews. The Script Example Output The result…

  • Testing Remote Server Port Connectivity with PowerShell

    Testing connectivity to remote server ports is a basic skill every DBA should have. Not just DBAs either — anyone running production systems needs to be able to answer one simple question quickly: Can this server actually reach that server on this port? This post shows two reliable ways to test whether a remote port…

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)