Transaction Log Archives - Page 12 of 15 - SQL Authority with Pinal Dave

A single incense coil burns at one tip while its long unburned continuation remains intact

Why a Second Log File Does Not Make Anything Faster

March 20, 2012

SQL Data Storage, SQL DMV, Transaction Log, VLF

Adding a second log file feels like a speed fix, but it is only a spare room. Here is how to watch it work and how to retire it.

Two hourglasses on a desk by a window, one tied with a red ribbon, beside a loose coil of rope.

Accelerated Database Recovery Quiz: Why Did the Rollback Finish at Once?

January 24, 2012

Pinal Dave

SQL Puzzle

SQL Performance, SQL Server 2019, SQL Transactions, Transaction Log

This Accelerated Database Recovery Quiz asks how long a rollback takes when ADR is on. The answer is almost no time, and a tested script measures the same rollback with ADR off and on. It also shows what ADR costs.

A slate board on an easel, partly wiped clean, with a sponge resting on the ledge.

CHECKPOINT Behavior Quiz: What Lets the Log Be Reused?

January 17, 2012

Pinal Dave

SQL Puzzle

SQL Backup and Restore, SQL Log, SQL Server Configuration, Transaction Log

This CHECKPOINT Behavior Quiz asks what lets SQL Server reuse transaction log space under the SIMPLE and FULL recovery models. The answers differ, and a tested script shows both. It also shows how one open transaction keeps the log from being reused.

A line of footprints in fresh snow crossing a courtyard to a closed door, with no one in sight.

Reading the Transaction Log With fn_dblog

September 29, 2011

Pinal Dave

SQL Tips and Tricks

SQL Audit, SQL Backup and Restore, SQL Server, Transaction Log, Undocumented SQL

fn_dblog can expose transaction-log records during a narrow investigation, but retention and missing user context make it a last resort.

Index Write Cost: What Each New Index Adds to an INSERT

January 17, 2011

Pinal Dave

SQL Performance

SQL Index, SQL Performance, Transaction Log

Index write cost is the part of the create-an-index advice that the cartoon leaves out. This test inserts the same 20,000 rows with no extra index, one and three, and measures the log written each time. It also shows what the index saved on a lookup, so you can weigh both sides.