I have this T-SQL that works just fine and it executes about 30 seconds.
DECLARE @BatchSize int = 1000,
@TransactionInterval tinyint = 5,
@MaxToDelete int = 10000,
@FactDeleted int = 0;
DECLARE @counter int = 1;
DECLARE @s datetime,
@e datetime,
@r int = 1;
SET @e = '20200606';
SELECT
@s = MIN(AtTime)
FROM
dbo.DevicePositions;
BEGIN TRANSACTION;
WHILE (@r > 0)
BEGIN
IF @r % @TransactionInterval = 1
BEGIN
COMMIT TRANSACTION;
BEGIN TRANSACTION;
END
DELETE TOP (@BatchSize)
FROM DevicePositions
WHERE AtTime >= @s
AND AtTime <= @e;
SET @FactDeleted = @FactDeleted + @BatchSize;
SET @r = @@ROWCOUNT;
SET @counter = @counter + 1;
IF @FactDeleted >= @MaxToDelete
BREAK;
END
IF @@TRANCOUNT > 0
BEGIN
COMMIT TRANSACTION;
IF @counter % 10 = 0 -- or maybe 100 or 1000
BEGIN CHECKPOINT;
END
END
GO
SELECT
A.Records
FROM
(SELECT
OBJECT_NAME(object_id) as ID,
SUM(row_count) AS Records
FROM
sys.dm_db_partition_stats
WHERE
object_id = OBJECT_ID('DevicePositions')
AND index_id < 2
GROUP BY
OBJECT_NAME(object_id)) A
I converted this code into a stored procedure and it won't complete so it runs forever.
The stored procedure:
ALTER PROCEDURE [dbo].[DeleteOldDevicePositions]
@MaxToDelete int , -- Max amount of records to delete
@BatchSize int , -- it is
@ToEndDate datetime -- Delete until this datetime
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TransactionInterval tinyint = 5, @FactDeleted int = 0;
DECLARE @counter int = 1;
DECLARE @s datetime, @e datetime, @r int = 1;
SELECT @s = MIN(AtTime) FROM dbo.DevicePositions;
BEGIN TRANSACTION;
WHILE (@r > 0)
BEGIN
IF @r % @TransactionInterval = 1
BEGIN
COMMIT TRANSACTION;
BEGIN TRANSACTION;
END
DELETE TOP (@BatchSize) FROM DevicePositions WHERE AtTime >= @s AND AtTime <= @ToEndDate;
SET @FactDeleted = @FactDeleted +@BatchSize;
SET @r = @@ROWCOUNT;
SET @counter = @counter + 1;
IF @FactDeleted >= @MaxToDelete
BREAK;
END
IF @@TRANCOUNT > 0
BEGIN
COMMIT TRANSACTION;
IF @counter % 10 = 0 -- or maybe 100 or 1000
BEGIN
CHECKPOINT;
END
END
SELECT A.Records FROM (
SELECT OBJECT_NAME(object_id) as ID, SUM(row_count) AS Records FROM sys.dm_db_partition_stats WHERE
object_id = OBJECT_ID('DevicePositions') AND index_id < 2
GROUP BY OBJECT_NAME(object_id) ) A
END
And I start it like
EXEC [dbo].[DeleteOldDevicePositions] 10000, 1000, '20200606'
So it starts and has no end.
What did I miss?
Thank you!
