【TechTarget中国原创】DELETE命令和事务日志增长
这次的目标和前面一样。我向表中插入10，000行并运行以下代码来一次删除一行：
-- Truncate the table
truncate table ExpandDB
go
-- Truncate the T-Log
backup transaction ShrinkDB with truncate_only
go
-- Shrink T-Log back to 2MB:
DBCC SHRINKFILE (N'ShrinkDB_Log' , 0, TRUNCATEONLY)
Go
-- Insert 100000 rows
declare @i int
set @i = 1
while @i <= 10000
begin
insert into ExpandDB select replicate ('a',1000)
set @i = @i + 1
end
go
-- Delete rows one by one:
set rowcount 1
while (select size from sysfiles where fileid = 2) delete from ExpandDB
set rowcount 0
go |
出现了同样的情况：事务日志并没增长，空间循环地填充和清空。 然后我尝试了大量的删除：
-- Truncate the table
truncate table ExpandDB
go
-- Truncate the T-Log
backup transaction ShrinkDB with truncate_only
go
-- Shrink T-Log back to 2MB:
DBCC SHRINKFILE (N'ShrinkDB_Log' , 0, TRUNCATEONLY)
Go
-- Insert 100000 rows
declare @i int
set @i = 1
while @i <= 10000
begin
insert into ExpandDB select replicate ('a',1000)
set @i = @i + 1
end
go
-- Delete 10,000 at a time and monitor the T-Log size and free space:
begin tran
set rowcount 10000
while begin
delete from ExpandDB
dbcc sqlperf(logspace)
end set rowcount 0 commit tran go |
结果表明事务日志增长：
最后我运行了一个有少量操作的大量事务：
-- Truncate the table
truncate table ExpandDB
go
-- Truncate the T-Log
backup transaction ShrinkDB with truncate_only
go
-- Shrink T-Log back to 2MB:
DBCC SHRINKFILE (N'ShrinkDB_Log' , 0, TRUNCATEONLY)
Go
-- Insert 100000 rows
declare @i int
set @i = 1
while @i <= 10000
begin
insert into ExpandDB select replicate ('a',1000)
set @i = @i + 1
end
go
-- Delete 10,000 at a time and monitor the T-Log size and free space:
begin tran
set rowcount 1
while from ExpandDB) begin delete from ExpandDB end |
结果显示事务日志增长了，尽管很慢。
这一切意味着什么
当执行少量分散的INSERT, UPDATE和DELETE命令时，事务日志并不会增长太多。但当我浏览事务日志文件时，这一测试生成了奇怪的动作：当达到大约70%时，文件填充清空但不增长。
当大量操作被执行时，包括事务中的大量数据和事务日志都增长，但并不是预期的线性增长。似乎有些数据在事务中被压缩了。