Deleting millions of logs in batches 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 &gt; 0
begin
    begin transaction;  
        delete top(10000)   
        from [Logs]
        where 
            cast([Timestamp] as date) &lt; cast(dateadd(day, -7, GETUTCDATE()) as date))
        set @r = @@ROWCOUNT;
    commit transaction;
end


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,
 CONSTRAINT [PK_Log_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO




Is there a way to make this script faster? Am I doing anything terribly wrong here?
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:

Snapshot isolation
Not using TOP
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:
DELETE FROM [Logs]
  WHERE Id BETWEEN @LowestCurrentIndex AND @HighestCurrentIndex
    AND CAST([Timestamp] AS date) &lt; 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:
SELECT Id
  INTO #OldData
  FROM [Logs]
  WHERE CAST([Timestamp] AS date) &lt; 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.
DELETE [Logs]
  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:

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

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

Copy all of the valid data into another identically formatted table (same indices, constraints, etc)
Rename the original table to something else
Rename the table from #1 to the original table's name
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.