# Investigate SQL Log Io Pressure

> Use when commits are slow or log writes lag: WRITELOG waits, high log write percent, log throughput throttling, too many VLFs. For PAGEIOLATCH or data-file reads, use `investigate-sql-data-io-pressure`.

- Skill: `microsoft/investigate-sql-log-io-pressure` (Agent Skill, multi-file: 4 files)
- Install (CLI): `npx get-skill add microsoft/investigate-sql-log-io-pressure`
- Raw SKILL.md: https://api.skillmd.com/api/skills/microsoft/investigate-sql-log-io-pressure/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: Microsoft (https://skillmd.com/u/microsoft)
- Updated: 2026-11-18
- Page: https://skillmd.com/skills/microsoft/investigate-sql-log-io-pressure

---


# Log IO Pressure Investigation

> For **data IO pressure** (PAGEIOLATCH waits, physical reads, buffer pool issues),
> use the **`investigate-sql-data-io-pressure`** skill instead.

## Scope

- Confirms sustained log IO pressure, then identifies log-generating sessions and checks log file latency, VLF health, and transaction patterns.
- For a broad CPU, memory and IO resource usage report, use the `resource-usage-sqlserver` skill.

## Required tools

- A read-only SQL query tool connected to the target server and database. Prefer, in order: the SQL
  Tools MCP server that ships with the `microsoft-sql-admin` plugin
  (`mssql_list_database_connections`, `mssql_connect_database`, then `mssql_execute_query`); any
  other SQL tool the agent host provides for running T-SQL and reading result sets;
  [`sqlcmd`](https://learn.microsoft.com/sql/tools/sqlcmd/sqlcmd-utility) from a terminal. If the
  SQL Tools MCP server is not connected or lists no connections, follow the `sql-tools-mcp-setup`
  skill, which asks before installing or configuring anything. If no SQL tool can be made available,
  give the user the T-SQL to run and ask for the results.
- Least privilege: connect that tool with a diagnostic login that cannot write, for example one that
  holds only `VIEW SERVER PERFORMANCE STATE` (`VIEW SERVER STATE` before SQL Server 2022) or, on Azure
  SQL Database, `VIEW DATABASE PERFORMANCE STATE` or `##MS_ServerStateReader##`, plus any other read
  permission these queries need. A tool is only as read-only as the login it connects with.

## Prerequisites

- Must be connected to the target database.
- Confirm which server and database you are connected to (e.g. `SELECT @@SERVERNAME, DB_NAME(), SUSER_SNAME(), SERVERPROPERTY('EngineEdition')`).
- Step 2 queries live DMVs; run while log IO pressure is active.

## Parameters

| Parameter | Default | Description |
|-----------|---------|-------------|
| TopN | 10 | Number of top sessions/queries to return in each step |
| LogIoThreshold | 80 | Log write percentage threshold for confirming pressure (Azure SQL) |
| ConfirmLookbackMinutes | 10 | Window (minutes) for the resource stats confirmation check |
| HistoryLookbackHours | 12 | Lookback window (hours) for the plan-cache history query |
| MinExecutionCount | 10 | Minimum execution count for a plan to appear in Step 5 |
| MinTotalLogicalWrites | 50000 | Minimum cumulative `total_logical_writes` for Step 5 |

## Remediation Safety

- All diagnostic queries in this skill are read-only.
- State-changing commands in the references (`DBCC SHRINKFILE` and `ALTER DATABASE ... MODIFY FILE`)
  are **recommendations only**.
- Never run any of them without the user's explicit confirmation for that specific command. A
  request to investigate, diagnose, or "fix" the problem is not that confirmation.
- Before asking, explain the impact: shrinking a log file causes regrowth, and growth events stall
  writes. Recommend a maintenance window and a fixed target size rather than shrinking to a minimum.

## Investigation Workflow

Execute all steps in order. Step 2 targets live sessions — run while log IO pressure is active.
Steps 3–5 cover cumulative or historical data and can be run at any time.

---

### Step 1: Detect Platform and Confirm Log IO Pressure

> Execute with the SQL query tool. Detects the SQL Server engine edition at runtime and routes to
the appropriate DMV to confirm sustained **log IO** pressure.

| EngineEdition | Platform | DMV | Notes |
|---|---|---|---|
| 5 | Azure SQL Database | `sys.dm_db_resource_stats` | `avg_log_write_percent` — ~15s intervals, ~1h history |
| 8 | Azure SQL Managed Instance | `sys.dm_db_resource_stats` | Same as Azure SQL DB |
| 12 | Fabric SQL Database | `sys.dm_db_resource_stats` | Behaves like Azure SQL Database |
| 6 | Azure Synapse Analytics (Dedicated SQL pool) | N/A | Not supported — log IO metrics differ significantly in DW |
| 11 | Azure Synapse serverless SQL pool / Fabric Warehouse | N/A | Not supported — log IO metrics differ significantly in DW/Warehouse environments |
| other | SQL Server on-prem / IaaS | `sys.dm_os_wait_stats` | Cumulative WRITELOG waits since restart |

> **Query:** See [references/queries.md#step-1-detect-platform-and-confirm-log-io-pressure](references/queries.md#step-1-detect-platform-and-confirm-log-io-pressure)

**Interpret:** Azure SQL: `avg_log_write_percent >= threshold` = confirmed (near 100% = hitting log rate limit). On-prem: WRITELOG waits are **cumulative since restart**. High `writelog_wait_count` = frequent small commits.

| Assessment | Action |
|------------|--------|
| Log IO pressure confirmed | Proceed to Step 2 — identify active log generators |
| Intermittent spikes | Steps 2–3 to catch offenders; Step 4 for log space/VLF issues |
| No sustained pressure | Skip Step 2. Steps 3–5 for historical and structural analysis |
| `is_unsupported_dw = 1` | **Stop** — this skill does not apply to Azure Synapse (edition 6) or Synapse serverless / Fabric Warehouse (edition 11). Fabric SQL Database (edition 12) is supported. |

---

### Step 2: Real-Time Log IO Consumers — Sessions with WRITELOG Waits and Log Bytes

> Execute with the SQL query tool. Shows active sessions generating transaction log IO. Joins
`sys.dm_tran_session_transactions` and `sys.dm_tran_database_transactions` to show actual
**log bytes used** per session — a direct measure of log generation, not just write counts.

> **Query:** See [references/queries.md#step-2-real-time-log-io-consumers--sessions-with-writelog-waits-and-log-bytes](references/queries.md#step-2-real-time-log-io-consumers--sessions-with-writelog-waits-and-log-bytes)

**Interpret:** `log_bytes_used` = actual log generated by session's transaction. `log_mb_used > 10` = significant. `txn_duration_sec` = how long transaction has been open (holds log space). `log_bytes_reserved` = space reserved for rollback.

| Finding | Recommendation |
|---------|----------------|
| `WRITELOG` wait + high `log_bytes_used` | Active log generator hitting flush latency — check Step 3 |
| `log_mb_used` > 100 MB | Large transaction — batch into smaller transactions |
| Long `txn_duration_sec` (> 300s) | Long-running transaction preventing log truncation — review design |
| Many small `log_bytes_used` all on WRITELOG | Frequent small commits — batch them |

---

### Step 3: Log File Latency

> Execute with the SQL query tool. Measures log file IO latency from `sys.dm_io_virtual_file_stats`,
filtered to **LOG** files only. This isolates log write performance from data file performance.

> **Azure SQL context:** In Azure SQL Database, log write metrics reflect the workload behavior within
the service tier's **log rate governance** limits. High write latency typically indicates the database is
approaching or hitting its maximum log throughput — not that the underlying storage is slow.

> **Query:** See [references/queries.md#step-3-log-file-latency](references/queries.md#step-3-log-file-latency)

**Interpret:** `avg_log_write_latency_ms > 5` = slow log flushes (healthy OLTP < 2ms). `avg_bytes_per_write < 4KB` = many tiny commits. Values are **cumulative since restart**.

| Finding | Recommendation |
|---------|----------------|
| `avg_log_write_latency_ms > 5` (on-prem) | Dedicate a fast drive (SSD/NVMe) for transaction log |
| `avg_log_write_latency_ms > 5` (Azure SQL) | Hitting log throughput limit — scale up tier |
| Small `avg_bytes_per_write` (< 4KB) | Too many small commits — batch DML operations |
| High `log_reads` | Frequent rollbacks or replication reads — investigate aborted transactions |

---

### Step 4: Transaction Log Space, VLF Health, and Reuse Blockers

> Execute with the SQL query tool. Analyzes transaction log space usage, Virtual Log File (VLF) count,
and identifies what is preventing log truncation. Excessive VLFs cause log IO overhead; log reuse
blockers cause log growth.

#### Step 4a: Log Space Usage

> **Note:** `log_space_in_bytes_since_last_backup` requires SQL Server 2014+ or Azure SQL.
On older versions (SQL 2012), this column is omitted.

> **Query:** See [references/queries.md#step-4a-log-space-usage](references/queries.md#step-4a-log-space-usage)

#### Step 4b: VLF Count and Log Stats

> **Note:** `sys.dm_db_log_stats` requires SQL Server 2016 SP2+ or Azure SQL. On older versions,
this step returns a compatibility message.

> **Query:** See [references/queries.md#step-4b-vlf-count-and-log-stats](references/queries.md#step-4b-vlf-count-and-log-stats)

#### Step 4c: Active Transactions Holding Log Space

> **Query:** See [references/queries.md#step-4c-active-transactions-holding-log-space](references/queries.md#step-4c-active-transactions-holding-log-space)

**Interpret:**

- **4a:** `used_log_space_pct > 80%` = log mostly full. `log_since_last_backup_mb` high = infrequent log backups.
- **4b:** `total_vlf_count > 1000` = excessive VLFs causing IO overhead. `log_truncation_holdup_reason`: `LOG_BACKUP` = need log backup; `ACTIVE_TRANSACTION` = long-running txn; `REPLICATION`/`AVAILABILITY_REPLICA` = secondary lagging; `NOTHING` = healthy.
- **4c:** High `txn_duration_sec` + `log_mb_used` = holding log space, preventing truncation.

| Finding | Recommendation |
|---------|----------------|
| `used_log_space_pct > 80%` | **On-prem/MI:** Log backup or check truncation blockers. **Azure SQL DB/Fabric:** Check for long-running txns or replication lag |
| `total_vlf_count > 1000` | Shrink log, regrow with fixed increments (e.g., 1GB FILEGROWTH) |
| `LOG_BACKUP` holdup | **On-prem/MI:** Schedule more frequent log backups. **Azure SQL DB/Fabric:** Service-managed — contact support if persistent |
| `ACTIVE_TRANSACTION` holdup | Resolve the long-running transaction from Step 4c |
| Long-running transactions (> 5 min) | Review application code for missing COMMIT/ROLLBACK |

---

### Step 5: Log-Intensive Patterns — Historical Write-Heavy Queries

> Execute with the SQL query tool. Identifies queries from the plan cache with the highest cumulative
`total_logical_writes`, which generate the most transaction log. These are the primary candidates for
batching or rewriting to reduce log IO pressure.

> **Query:** See [references/queries.md#step-5-log-intensive-patterns--historical-write-heavy-queries](references/queries.md#step-5-log-intensive-patterns--historical-write-heavy-queries)

**Interpret:** `logical_writes_per_exec > 1000` = write-heavy query. High `execution_count` + moderate writes = massive aggregate log generation. `max_logical_writes >> avg` = variable, possibly parameter-sensitive.

| Finding | Recommendation |
|---------|----------------|
| `logical_writes_per_exec` > 10K | Batch into smaller chunks (5K–10K rows per batch with COMMIT between) |
| High execution count + moderate writes | Wrap related operations in explicit transactions to reduce flush frequency |
| INSERT-heavy workloads | Consider minimal logging (BULK INSERT with TABLOCK in SIMPLE/BULK_LOGGED) |

---

## Report Output

After completing all steps, read [references/report-template.md](references/report-template.md) and present the investigation results using that template.

## Root Cause Assessment

Use [references/root-cause-guide.md](references/root-cause-guide.md) to determine the root cause and recommended next steps based on findings from all steps.

