Хранилище версий не очищается, если хоть в одной базе есть открытые транзакции

Автор: Brent Ozar, The Version Store Won’t Clear If ANY Database Has Open Transactions

Это особенно болезненно для тех, кто обслуживает несколько баз данных на одном сервере. Достаточно одному приложению вести себя плохо и оставлять транзакции открытыми — и внезапно остальные базы начинают расширять TempDB за пределы доступного места. Причём та транзакция в злосчастном приложении может даже ничего не менять, и раньше сама по себе проблем не вызывала, — но как только она делит TempDB с другими приложениями, начинаются каскадные эффекты.

Начнём с создания базы и загрузки в неё данных:

CREATE DATABASE ShouldBeIsolated;
GO
ALTER DATABASE ShouldBeIsolated
    SET READ_COMMITTED_SNAPSHOT ON;
GO
USE ShouldBeIsolated;
GO
CREATE TABLE dbo.Customers
    (Id INT IDENTITY(1,1),
     Balance INT,
     CustomerName VARCHAR(6000));
INSERT INTO dbo.Customers (Balance, CustomerName)
    SELECT 0, REPLICATE('Yo', 2500)
    FROM generate_series(1,10000);
GO

Проверим, сколько места сейчас занято хранилищем версий — должно быть ноль:

SELECT DB_NAME(database_id) AS database_name,
  reserved_space_kb / 1024.0 AS version_store_mb
FROM sys.dm_tran_version_store_space_usage
WHERE reserved_space_kb > 0
ORDER BY 2 DESC;

Сделаем «неизменяющее» обновление и снова проверим хранилище версий:

UPDATE dbo.Customers
    SET Balance = Balance;
/* A copy goes into the version store: */
SELECT DB_NAME(database_id) AS database_name,
  reserved_space_kb / 1024.0 AS version_store_mb
FROM sys.dm_tran_version_store_space_usage
WHERE reserved_space_kb > 0
ORDER BY 2 DESC;

Несмотря на то, что наша команда UPDATE не была внутри транзакции и запрос уже завершился, вы всё равно на короткое время увидите копию таблицы в хранилище версий.

Подождите секунд тридцать и проверьте ещё раз. Фоновые процессы очистят хранилище автоматически. Так и должно работать в здоровом сценарии.

А теперь представьте, что вы поставили на тот же сервер стороннее приложение — у него своя база:

CREATE DATABASE BadlyBehaved;
GO
ALTER DATABASE BadlyBehaved
    SET READ_COMMITTED_SNAPSHOT ON;
GO
USE BadlyBehaved;
GO
CREATE TABLE dbo.Customers
    (Id INT IDENTITY(1,1),
     Balance INT,
     CustomerName VARCHAR(6000));
INSERT INTO dbo.Customers (Balance, CustomerName)
    SELECT 0, REPLICATE('Yo', 2500)
    FROM generate_series(1,10000);
GO
BEGIN TRAN;
UPDATE dbo.Customers
    SET Balance = Balance;
GO
/* How much space is used in the version store right now? */
SELECT DB_NAME(database_id) AS database_name,
  reserved_space_kb / 1024.0 AS version_store_mb
FROM sys.dm_tran_version_store_space_usage
WHERE reserved_space_kb > 0
ORDER BY 2 DESC;

Да, «плохое» приложение оставило транзакцию открытой. Но какая разница? Это же касается только их базы, а не нашей изолированной, верно?

Вот в чём проблема: переключитесь обратно на нашу якобы изолированную базу, повторите однострочный UPDATE и снова посмотрите на использование пространства хранилища версий.

Ждите сколько угодно, но размер нашего хранилища версий не будет уменьшаться — даже если базы никак между собой не связаны и наше дисциплинированное приложение не использует транзакций. Любые UPDATE и DELETE в ShouldBeIsolated будут попадать в версионное хранилище и оставаться там, пока приложение BadlyBehaved не закоммитит или не откатит свои транзакции.

А если включить ADR в BadlyBehaved?

Параметр уровня базы Accelerated Database Recovery переносит версионное хранилище RCSI из общего TempDB в саму пользовательскую базу, что ускоряет откаты. Откатим транзакцию в BadlyBehaved, подождём, пока у всех очистится TempDB, затем включим ADR в BadlyBehaved:

ROLLBACK;
GO
ALTER DATABASE BadlyBehaved
    SET ACCELERATED_DATABASE_RECOVERY = ON;
GO
BEGIN TRAN;
UPDATE dbo.Customers
    SET Balance = Balance;
GO
/* Check the TempDB version store: */
SELECT DB_NAME(database_id) AS database_name,
  reserved_space_kb / 1024.0 AS version_store_mb
FROM sys.dm_tran_version_store_space_usage
WHERE reserved_space_kb > 0
ORDER BY 2 DESC;

Теперь BadlyBehaved вовсе не занимает места в TempDB.

А если снова переключиться на нашу «изолированную» базу, выполнить обновление и посмотреть использование TempDB:

Да, таблица изначально попадает в хранилище версий — так и должно быть при RCSI — но подождите тридцать секунд и проверьте ещё раз — и...

Плохие новости. ADR тут не помогает. Ещё раз: если хоть в одной базе есть открытые транзакции, даже если в этой базе включён ADR,  хранилище версий в TempDB само себя не очистит.

Примечание: этот пост вдохновлён записью Sunil Agarwal на MSDN от 2008 года (Managing TempDB in SQL Server: TempDB Basics (Version Store: Growth and removing stale row versions)) и ныне удалённой публикацией неизвестного автора с Kohera.be. Kohera несколько раз меняли URL той записи, а потом и вовсе удалили её; у MSDN, увы, похожие традиции. Поэтому я решил зафиксировать это у себя, раз уж упоминаю проблему на курсе «Fundamentals of TempDB».