Today, I had to perform a DELETE that happened to remove quite a bit of data. This caused two issues for me
- Log file growth which fills up the Transaction Log of the DB
- 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
- Create a temporary table with just the data we need to keep
- Truncate the original table
- Copy data back from temporary table back to the original
- 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
3 thoughts on “SQL Server: Delete In Chunks To Avoid Log Growth”