T-SQL script for deleting big amount of records from table in SQL server database

Sometimes we need to delete big amount of records from the table in SQL Server database. If number of records is quite big (several millions) the simple delete command will hang:

   1: delete from Foo

In this case we can use the following approach: delete records by chunks (e.g. 100000 items per operation, but it depends on how many columns your table has. It if has a lot of columns and data, you may adjust this number for your scenario) until all records will be deleted. For example imagine that we have table [Log] used for logging some operations:

   1: create table [Log](

   2:     [Id] [int] identity(1,1) not null,

   3:     [ThreadId] [int] not null,

   4:     [EventType] [nvarchar](32) null,

   5:     [Message] [nvarchar](max) null,

   6:     [Created] [datetime] not null

   7: )

And suppose that this table grows quite fast and we need periodically delete old log records from it (e.g. older than 2 weeks). It can be done by creating job in SQL Server agent. For this job create the step which will run the following code:

   1: declare @s datetime

   2: set @s = GETDATE() - 14

   3: declare @cnt int

   4:  

   5: set @cnt = (select count(*) from [Log] where Created < @s)

   6: while @cnt > 0

   7: begin

   8:     delete top (100000)

   9:     from [Log]

  10:     where Created < @s

  11:     

  12:     set @cnt = (select count(*) from [Log] where Created < @s)

  13: end

This script will delete records from the Log table by chunks in 100000 rows until all records which are older than 2 weeks won’t be deleted and won’t hang because of big amount of data.