Category: DBA Scripts
-
Script: Get SQL Server CPU Topology and Core Counts
This script gives a quick view of how SQL Server sees the CPU layout on a server. It returns the SQL Server version and edition, CPU count, sockets, cores per socket, NUMA node count, scheduler visibility, and a few related configuration values such as MAXDOP, cost threshold for parallelism, and CPU affinity settings. I find…
Written by
-
Script: Generate Test Databases
When you’re validating migrations, failover, or automation at scale, you need realistic volume: hundreds or thousands of databases to stress-test backups, restores, log shipping, and copy operations. This helper automates that setup, it creates batches of small, randomized databases with configurable sizes and naming, so you can reproduce large-scale scenarios quickly and repeatably.
Written by
-
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…
Written by
-
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.…
Written by
-
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…
Written by
-
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…
Written by
-
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…
Written by
-
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…
Written by
Blog Categories
Latest Posts
- Script: Get SQL Server CPU Topology and Core Counts
- Script: Generate Test Databases
- Troubleshooting Database Mirroring Issues in SQL Server
- Get Current Date & Time in SQL Server
- Creating SQL Logins on an Availability Group (AG) Environment
- Grant VIEW SERVER STATE in SQL Server
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)