Guru Guide To Sql Server Architecture: And Internals.pdf

That open transaction was preventing the transaction log from truncating. The log had grown to 200 GB. The ETL’s large update inside FactSales_Load had to wait for log space, causing log autogrowth events (zero-initialization → slow).

Here’s a story that teaches a real-world lesson from those internals. The Case of the Midnight Slowdown Guru Guide To Sql Server Architecture And Internals.pdf

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. That open transaction was preventing the transaction log

He ran:

SELECT last_user_seek, last_user_scan, modifications FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID('SalesDB') AND object_id = OBJECT_ID('Orders'); The result: last_user_seek was yesterday. modifications was over 50,000. Guru Guide To Sql Server Architecture And Internals.pdf