SQL Server: Delete In Chunks To Avoid Log Growth

Today, I had to perform a DELETE that happened to remove quite a bit of data. This caused two issues for me

  1. Log file growth which fills up the Transaction Log of the DB
  2. TempDB was getting full

To avoid both problems I deleted in chunks.

OPTION #1: DELETEing in Chunks is easier than you think – w/TOP x

Below is the DELETE that I want to perform (not chunked)

DELETE
FROM [MyDB].[dbo].[Logs]
WHERE DATEDIFF(day, TimeStampColName, GetDate()) > 7;

Below, I am deleting everything older than 7 days but doing so in chunks of 100 using TOP 100 in the DELETE (table had a couple of NVARCHAR(MAX) columns)!

WHILE 1=1
BEGIN
   DELETE TOP(100)
   FROM [MyDB].[dbo].[Logs]
   WHERE DATEDIFF(day, TimeStampColName, GetDate()) > 7;

   IF @@ROWCOUNT = 0
      BREAK;
END;

You could also use TOP(x) PERCENT if you wanted to delete a percent of the rows with each DELETE but in our case above, the TOP(x) is preferable.

For a more elaborate treatment of this, please see the very informative post by Aaron Bertrand below:
https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes

OPTION #2: DELETING without DELETING – Backup data to keep, Truncate, Restore

There is an alternative and faster way to DELETE data especially if there are no foreign key references and if the data to keep is very little

Before you waste time, check if the table to be truncated has foreign keys

EXEC sp_fkeys @pktable_name = 'YourTableName', @pktable_owner = 'dbo'

Proceed if there are no FK’s

  1. Create a temporary table with just the data we need to keep
  2. Truncate the original table
  3. Copy data back from temporary table back to the original
  4. Drop the temporary table

Here is an example of how that is done:


--Create a new table with the data we need to keep
-----------------------------------------------
SELECT *
INTO [MyDB].[dbo].[LogsTemp]
FROM [MyDB].[dbo].[Logs]
	WHERE DATEDIFF(day, TimeStamp, GetDate()) <= 7;

--Truncate the big table
-----------------------------------------------
TRUNCATE TABLE [MyDB].[dbo].[Logs];

--Turn on IDENTITY_INSERT
-----------------------------------------------
--Msg 8101, Level 16, State 1, Line 15
--An explicit value for the identity column in table 'UiPath.dbo.Logs' can only be specified when a column list is used and IDENTITY_INSERT is ON.
-- Statement Allows explicit values to be inserted into the identity column of a table.
SET IDENTITY_INSERT [MyDB].[dbo].[Logs] ON
GO

--Restore the data back
-----------------------------------------------
INSERT [MyDB].[dbo].[Logs]([Id]
      ,[OrganizationUnitId]
      ,[TenantId]
      ,[TimeStamp]
      ,[Level]
      ,[WindowsIdentity]
      ,[ProcessName]
      ,[JobKey]
      ,[RobotName]
      ,[Message]
      ,[RawMessage])
SELECT * FROM [MyDB].[dbo].[LogsTemp];

--Turn off IDENTITY_INSERT
-----------------------------------------------
SET IDENTITY_INSERT [MyDB].[dbo].[Logs] OFF
GO