This is SQL Server 2014 SP 2
The table being purged has a Columnstore Index.
The purge criteria is a smalldatetime column based on the # of days to keep, which is passed as a parameter.
declare @curDate smalldatetime;
declare @RowsDeleted int;
declare @TotalRowsDeleted int;
declare @LoopCtr int;
Set @curDate = getDate()
Set @RowsDeleted = 1;
Set @TotalRowsDeleted = 0;
Set @LoopCtr = 0;
SET NOCOUNT ON;
WHILE @RowsDeleted> 0 – loop until all qualifying rows have been deleted
Set @LoopCtr = @LoopCtr + 1;
DELETE TOP (12000) from LogHistory.ServiceLog
where Datediff(day, CreatedDate, @curDate) > @DaysToKeep;
SET @RowsDeleted = @@ROWCOUNT;
SET @TotalRowsDeleted = @RowsDeleted + @TotalRowsDeleted;
The Problem: Recenlty, this job has not been able to complete, even after running for days. It doesn’t delete any rows. even though millions of rows qualify for deleting. I have updated state on the CreatedDate column.
What could be the problem?
Thanks for any tips.