2

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?

6
  • 1
    The log will contain the data that was deleted. Since you deleted data from different tables, the answer lies there. How wide were the tables, what were the data types? If you want to purge a table completely then you should be using TRUNCATE, although that will delete everything from a table. Mar 1, 2017 at 14:09
  • I only delete part of the tables and my DB is in full recovery so I can not use truncate. As far as I know TRUNCATE is not logged. Mar 1, 2017 at 14:15
  • Check if the second table had any ON DELETE CASCADE constraints. Mar 1, 2017 at 14:18
  • 2
    It is a total myth that TRUNCATE TABLE is not logged. If it were not logged, you wouldn't be able to roll it back. It is logged differently, but it is not "not logged." Mar 1, 2017 at 14:30
  • As for the question, yes, I would look at size of data not number of rows. Number of indexes might also be relevant in your case. Mar 1, 2017 at 14:33

1 Answer 1

4

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.