I have a fast growing table with logs that I frequently have to delete (the server doesn't have much resources). It grows by at least two million entries every day. The database is a SQL Server 2012.

Currently I use this script to delete entries older than 7 days:

set nocount on;
declare @r int = 1
while @r > 0
    begin transaction;  
        delete top(10000)   
        from [Logs]
            cast([Timestamp] as date) < cast(dateadd(day, -7, GETUTCDATE()) as date))
        set @r = @@ROWCOUNT;
    commit transaction;

When I don't do it daily then it runs for a couple of hours (3-5) blocking other tasks that usually exit with timeouts (they need it for reporting).

I use transactions because I sometimes need to stop it before it's finished and this is easier to do with smaller batches. Otherwise there is too much to rollback.

The table is a pretty normal log:

CREATE TABLE [dbo].[Log](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Timestamp] [datetime2](7) NOT NULL,
    [Environment] [nvarchar](50) NOT NULL,
    [Logger] [nvarchar](50) NOT NULL,
    [Message] [nvarchar](max) NULL,
    [Exception] [nvarchar](max) NULL,
    [Id] ASC

Is there a way to make this script faster? Am I doing anything terribly wrong here?

  • 1
    \$\begingroup\$ How about computing cast(dateadd(day, -7, GETUTCDATE()) as date) once and reuse it? \$\endgroup\$
    – Heslacher
    Jul 30, 2019 at 9:09
  • 1
    \$\begingroup\$ If this is a production environment, I would suggest to use a commercial tool that handles billions of logs for you: splunk.com \$\endgroup\$
    – dfhwze
    Jul 30, 2019 at 9:18
  • 1
    \$\begingroup\$ If a commercial tool is not an option, consider creating an unclustered index on [Timestamp]. \$\endgroup\$
    – dfhwze
    Jul 30, 2019 at 9:20
  • 1
    \$\begingroup\$ some other options: stackoverflow.com/questions/24213299/… -> it also depends on whether you are deleting most of the records from the table, or keeping most. \$\endgroup\$
    – dfhwze
    Jul 30, 2019 at 15:58
  • 1
    \$\begingroup\$ @dfhwze I have an idea! I'll put all ids that I want to delete into a cts and then make a delete + join with that on the index without searching each time. \$\endgroup\$
    – t3chb0t
    Jul 30, 2019 at 16:02

1 Answer 1


Here is another recent Code Review question that has a lot of similarities to yours: Daily SQL job to delete records older than 24 hours.

I think I would recommend three things here:

  1. Snapshot isolation
  2. Not using TOP
  3. Doing the work in another table

Snapshot isolation

Snapshot isolation is a really cool tool that effectively prevents most things from blocking readers. As a result, even if your delete is running long, report-writers can still hit your table and they'll see the most recent, valid data. Then once your delete finishes, they'll start seeing the data without your deleted rows. This lets you do whatever you need to do without having to worry as much about end-users. It doesn't make your code faster, but it will somewhat reduce the need for it to be.

Not using TOP

Because you're using TOP, you effectively force all of the data to be re-sorted every time. Additionally, TOP will usually introduce row goals. This isn't necessarily a bad thing, but it may choose less-ideal plans in the interest of getting a subset of rows as quickly as possible.

Because your Id column is an indexed IDENTITY column, and because we're deleting the oldest data (I'm assuming you don't generally update old data), you can do something like this:

  WHERE Id BETWEEN @LowestCurrentIndex AND @HighestCurrentIndex
    AND CAST([Timestamp] AS date) < CAST(DATEADD( DAY, -7, GETUTCDATE()) AS date);

This assumes you can maintain @LowestCurrentIndex and @HighestCurrentIndex as the range of values to currently consider. This will get you nice index accesses as well.

A potential enhancement is to get a separate table that has all potentially affected rows, like so:

  INTO #OldData
  FROM [Logs]
  WHERE CAST([Timestamp] AS date) < CAST(DATEADD( DAY, -7, GETUTCDATE()) AS date);

Then you can just join between the two (and if you have an index on #OldData.Id it'll be a great merge-join) with the same bounds logic.

  FROM [Logs]
    INNER JOIN #OldData OldData
      ON [Logs].Id = OldData.Id
  WHERE OldData.Id BETWEEN @LowestCurrentIndex AND @HighestCurrentIndex -- This could be on either table

Do the work in another table

If copying the data is less expensive than deleting it (very possible), then you may be able to more efficiently do the work with less user interruptions by doing all of your work in a separate table, and then switching the tables. Broadly speaking, the workflow would look like this:

  1. Copy all of the valid data into another identically formatted table (same indices, constraints, etc)
  2. Truncate the original table
  3. Perform a partition switch

Alternatively, if partition switching isn't your jam, you can do a very similar thing manually:

  1. Copy all of the valid data into another identically formatted table (same indices, constraints, etc)
  2. Rename the original table to something else
  3. Rename the table from #1 to the original table's name
  4. Truncate the original (now renamed) table from #2

These will result in much less index maintenance, and many of the operations can be done via minimal logging if well structured.

  • 1
    \$\begingroup\$ wow, this is a lot of interesting stuff! It'll take some time to study it and do experiments. I'll let you know when I implemented these suggestions. \$\endgroup\$
    – t3chb0t
    Aug 22, 2019 at 15:47

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.