Экстремальное увеличение размера файла журнала при удалении записей

begin tran;
delete from ...;
 
select
 sum(dt.database_transaction_log_bytes_used), sum(вt.database_transaction_log_bytes_used_system)
from
 sys.dm_tran_current_transaction ct join
 sys.dm_tran_database_transactions dt on dt.transaction_id = ct.transaction_id;
 
rollback;

set showplan_xml on;
go
delete from ...;
go
set showplan_xml off;
go

select log_reuse_wait_desc from sys.databases where database_id = db_id('Наименование БД');

use master;
set ansi_nulls, quoted_identifier, xact_abort on;
go
 
create database TestDel;
alter database TestDel set read_committed_snapshot off;
go
 
use TestDel;
go
 
create table dbo.t (id int identity, a int, s char(20));
insert into dbo.t
 (a, s)
 select top (100000)
  1, 'a'
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;
go
 
begin tran;
delete from dbo.t where a > 0;
 
select
 'read_committed_snapshot = off', sum(dt.database_transaction_log_bytes_used), sum(dt.database_transaction_log_bytes_used_system)
from
 sys.dm_tran_current_transaction ct join
 sys.dm_tran_database_transactions dt on dt.transaction_id = ct.transaction_id;
 
rollback;
go
 
alter database TestDel set read_committed_snapshot on;
go
 
begin tran;
delete from dbo.t where a > 0;
 
select
 'read_committed_snapshot = on', sum(dt.database_transaction_log_bytes_used), sum(dt.database_transaction_log_bytes_used_system)
from
 sys.dm_tran_current_transaction ct join
 sys.dm_tran_database_transactions dt on dt.transaction_id = ct.transaction_id;
 
commit;
go
 
use master;
drop database TestDel;
go

begin tran;
 
select AllocUnitName, sum([Log Record Length]) from sys.fn_dblog(null, null) group by AllocUnitName order by 2 desc;
delete ...
select AllocUnitName, sum([Log Record Length]) from sys.fn_dblog(null, null) group by AllocUnitName order by 2 desc;
 
commit;