Guru Guide To Sql Server Architecture And Internals.pdf May 2026

Alex updated stats:

The transaction log is a circular log. It can’t reuse space if any active transaction holds onto a VLFL (virtual log file) even if it’s old. Guru Guide To Sql Server Architecture And Internals.pdf

Index stats were stale. The query optimizer thought the scan was cheaper because it didn’t know the table had grown massively since the last stats update. Alex updated stats: The transaction log is a circular log

He ran:

SELECT * FROM sys.dm_os_buffer_descriptors WHERE database_id = DB_ID('SalesDB'); He saw that 40 GB of the buffer pool was filled with old data from a morning report. The ETL’s needed pages (the clustered index of Orders ) were being paged in from disk— couldn’t save it because the scan had already caused random I/O earlier. The query optimizer thought the scan was cheaper

Alex killed the orphaned transaction (after confirming with the dev), shrunk the log safely, and set up alerting for long-running open transactions.

He looked at sys.dm_tran_database_transactions during the ETL. One transaction had an old database_transaction_begin_time from 3 hours ago—an open transaction from a developer’s BEGIN TRAN in SSMS that was never committed or rolled back.