Tag: SQL Scripts

  • Script: List Sysadmin Role Members

    This script returns all logins that are members of the sysadmin fixed server role. It includes login type, whether the login is disabled, and default database information. This is commonly used during security reviews, onboarding and offboarding checks, and when validating privileged access in production environments. The Script Example Output The result set shows one…

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

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

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

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

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

  • Script: Identify Long-Running Queries

    This script returns currently executing queries ordered by elapsed time. It helps identify long-running requests that may be causing blocking, resource pressure, or user-facing delays. This is typically used during live performance incidents when users report slowness or when the instance appears busy. The Script Example Output The result set shows currently executing sessions ordered…

  • Script: Identify Missing Indexes

    This script returns the highest-impact missing index recommendations based on cumulative workload statistics. It helps identify tables where SQL Server believes additional indexes could significantly improve query performance. This is useful during performance tuning, workload reviews, and when investigating high read activity or inefficient query plans. The Script Example Output The result set shows the…

  • Script: Check Index Fragmentation Across All Databases

    This script checks index fragmentation levels across all online databases on the instance. It helps identify heavily fragmented indexes that may require maintenance. This is useful when reviewing index health, validating maintenance jobs, or troubleshooting performance issues related to inefficient index access. The Script Example Output The result set shows indexes across all user databases,…

  • Script: SQL Server User & Permissions Audit

    This script audits SQL Server users and permissions across all databases on an instance. It consolidates server-level logins, database users, Windows groups, and role memberships into a single result set. It is useful during security reviews, access audits, migrations, or when validating permissions after changes. The Script Example Output The result set shows consolidated role…

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)