主页 / dba / 问题 / 165840
Accepted
Bogdan Bogdanov
Asked: 2017-03-02 06:01:25 +0800 CST

删除相似数量的行如何导致截然不同的日志增长？

  • 772

我有（在我看来）SQL Server 日志增长的非常奇怪的行为。我有一个大约 8GB 日志大小的数据库。我备份了事务日志并开始删除大约 400 万行。日志文件没有增长。然后我尝试从另一个表中删除另外 490 万 - 日志文件增长到大约 26 GB 并填满了驱动器。

我设法解决了这个问题，但我很好奇 - 因为（据我所知）日志文件只包含删除记录的命令（在这种情况下），当数字时，2 个操作之间怎么会有这么大的差异受影响的行数仅相差 20%？

sql-server sql-server-2012

1 个回答

  1. Best Answer

    评论太多了，但只是为了证明与简单的行数相比，数据量对记录多少有更大的影响。

    首先，创建一个数据库，并备份它，这样我们就不是伪简单的了：

    CREATE DATABASE RLS;
GO
ALTER DATABASE RLS SET RECOVERY FULL;
GO
BACKUP DATABASE RLS TO DISK = 'c:\temp\rls.bak' WITH INIT;
GO
USE RLS;
GO

    现在，创建一个瘦表和一个宽表，并插入 2,000 行：

    CREATE TABLE dbo.skinnyTable(id int PRIMARY KEY);

CREATE TABLE dbo.wideTable(id int PRIMARY KEY,
  dt datetime2(7) NOT NULL DEFAULT SYSUTCDATETIME(), 
  n nchar(2000) NOT NULL DEFAULT USER_NAME(),
  x uniqueidentifier NOT NULL DEFAULT NEWID(),
  y uniqueidentifier NOT NULL DEFAULT NEWID(),
  z uniqueidentifier NOT NULL DEFAULT NEWID());

INSERT dbo.skinnyTable(id)
  SELECT TOP (2000) [object_id] FROM sys.all_objects ORDER BY [object_id];

INSERT dbo.wideTable(id)
  SELECT TOP (2000) [object_id] FROM sys.all_objects ORDER BY [object_id];

    备份日志两次：

    BACKUP LOG RLS TO DISK = 'c:\temp\rls1.trn' WITH INIT;
BACKUP LOG RLS TO DISK = 'c:\temp\rls2.trn' WITH INIT;

    有了清晰的日志，让我们删除每个表的一半（1,000 随机行）：

    ;WITH cte AS (SELECT TOP (1000) * FROM dbo.skinnyTable ORDER BY NEWID())
  DELETE cte;

;WITH cte AS (SELECT TOP (1000) * FROM dbo.wideTable ORDER BY NEWID())
  DELETE cte;

SELECT AllocUnitName, log_rows = COUNT(*) 
  FROM sys.fn_dblog (NULL, NULL) AS l
  WHERE AllocUnitName LIKE N'%[ey]Table%'
  GROUP BY AllocUnitName;

    我得到：

    AllocUnitName                                     log_rows
----------------------------------------------    --------
dbo.skinnyTable.PK__skinnyTa__3213E83F83E76BF2    2008
dbo.wideTable.PK__wideTabl__3213E83FEFF3F638      14991

    显然，与更瘦的表相比，从更宽的表中删除相同数量的行需要更多的日志空间。

    作为逻辑扩展，索引的数量和宽度也会影响这种差异。而且我什至没有尝试 LOB 数据，也没有调查AllocUnitNameNULL 所在的行。以上仅说明了 7 倍的差异。

    • 4

相关问题