Sql-server – How to deleting similar number of rows result in very different log growth

sql serversql-server-2012transaction-log

I have (in my opinion) very strange behavior of SQL Server log growth. I have a database with approximately 8GB log size. I made transaction log backup and started deleting about 4 million rows. The log file did not grow. Then I tried to delete another 4.9 million from another table – the log file grew to about 26 GB and filled the drive.

I managed to fix the situation, but I was curious – since (as far as I am aware) the log file contains only commands to delete records (in this case), how can there be such a big difference between 2 operations when the number of rows affected is only 20% different?

Best Answer

Way too much for a comment, but just to prove that the amount of data has a much bigger impact on how much gets logged, compared to simply the number of rows.

First, create a database, and back it up so we aren't in pseudo-simple:

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

Now, create a skinny table and a wide table, and insert 2,000 rows:

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 the log twice:

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

With a clear log, let's delete half of each table (1,000 random rows):

;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;

I get:

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

Clearly, there is much more log space required to delete the same number of rows from a wide table, when compared to a skinnier table.

As a logical extension, number and width of indexes can influence this difference as well. And I didn't even try LOB data, nor did I investigate the rows where AllocUnitName was NULL. The above alone demonstrates a 7X difference.

Related Question