Tag: Storage and Capacity

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

  • Track Database Growth Events Over Time in SQL Server

    SQL Server’s default trace captures database and log file growth events, but it’s short-lived. Once the trace rolls over or the instance restarts, that history is gone. If you want to understand growth patterns over time, react less to disk alerts, and stop guessing which databases are mis-sized, you need to store those events somewhere.…

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

  • Deleting Rows in Batches in SQL Server

    Deleting large volumes of data from a SQL Server table looks simple, but it can cause real problems if done carelessly. A single large DELETE can: When you’re cleaning up historical data or running routine maintenance, deleting rows in batches is usually the safest and most predictable approach. This post shows a simple, production-safe pattern…

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

  • Show Database Growth Events in SQL Server

    Database file growth events are easy to ignore until they become a problem. When a data or log file autogrows, SQL Server must extend the physical file on disk before continuing. During this operation, activity against that file can stall. If this happens frequently, it can introduce latency, increase I/O pressure, and in some cases…

  • How to Check Transaction Log Space Usage in SQL Server

    Monitoring transaction log space usage is a fundamental DBA task. If a log file runs out of space, transactions can fail, applications can stop, and in the worst cases databases can be taken offline. This post shows two practical ways to check log space usage in SQL Server, starting with a built-in command and then…

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

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)