Tag: SQL Scripts

Reusable SQL Server scripts for common DBA tasks, diagnostics, reporting, maintenance, and troubleshooting across production environments.

  • Script: I/O Usage by Database

    This script returns total read and write activity per database based on file-level I/O statistics. It helps identify which databases are generating the most disk activity on the instance. This is commonly used during performance troubleshooting, storage pressure investigations, and workload analysis. The Script Example Output The result set shows one row per database, including…

  • Get Database Sizes and Free Space in SQL Server

    Keeping track of database file sizes and available free space is a basic but essential part of SQL Server administration. If you don’t know how large your data and log files are, or how much free space they contain, capacity planning becomes guesswork. Problems tend to surface late, usually during growth events, backups, or unexpected…

  • Script: Check Worker Threads and Active Sessions

    This script shows the current SQL Server worker thread count along with all active sessions and requests. It provides a live view of CPU usage, blocking, waits, running commands, and transaction activity. This is a practical script for performance troubleshooting when an instance appears busy, unresponsive, or under pressure. The Script Example Output The first…

  • Script: Top Wait Statistics

    This script returns the top wait types since the last SQL Server restart or wait statistics clear, excluding common idle and background waits. It provides a quick high-level view of where the instance is spending time. This is often the first script run during performance investigations to identify whether the primary pressure is related to…

  • Get Database File Names and Paths in SQL Server

    As a DBA, you regularly need to know where your database files live, how big they are, and how they’re configured to grow. Whether you’re planning disk capacity, reviewing growth settings, troubleshooting space issues, or preparing for a migration, being able to quickly pull data and log file details across an instance is essential. This…

  • Script: Get SQL Server Agent Job Overview

    This script returns an overview of all SQL Server Agent jobs on an instance, including job category, owner, whether the job is enabled, the last run start time, last run duration, and the next scheduled run time. It’s a practical check when reviewing inherited environments, validating job ownership, identifying disabled jobs, or confirming schedules after…

  • Script: Get SQL Server Services Information

    This script returns details about the SQL Server and SQL Server Agent services running on an instance. It shows the service name, process ID, startup type, current status, service account, and whether instant file initialisation is enabled. This is a useful check when validating new builds, reviewing service accounts, or troubleshooting startup and permission-related issues.…

  • Script: Get Database Sizes and Free Space

    This script returns file-level size and free space information for all databases on a SQL Server instance. It shows how much space is allocated, how much is used, and how much free space remains inside each data and log file. This is a go-to check when reviewing storage usage, investigating unexpected growth, or validating capacity…

  • Script: Estimate Backup and Restore Completion Time

    This script shows the current progress and estimated completion time for running backup and restore operations. It’s intended to be used while an operation is already in progress, giving a clear view of how long it has been running and how much time is likely remaining. This is especially useful during incidents or maintenance windows…

  • Script: Check Backup Coverage Across All Databases

    This script returns the most recent full, differential, and transaction log backup times for every database on a SQL Server instance. It’s designed as a quick sanity check to validate backup coverage, identify gaps, and confirm maintenance jobs are running as expected across an environment. The Script Example Output The result set shows one row…