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 begin transaction; delete top(10000) from [Logs] where cast([Timestamp] as date) < 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?