locked
Need help to fix Batch Delete RRS feed

  • Question

  • We are seeing issues on Prod server lately.

    We have a Job that inserts Real time data in to staging Table.We have another job that would delete anything that is 27 Hours Old in batches. We have billions of records for Insert and Deletes.

    Insert is failing and causing blocking deadlock issues for other jobs. I have posted sample SQL we have below. How can we fix this issue?

    F @rowCount = 1000000 -- if the last batch deleted < 1000000 rows there is no work to do...
    BEGIN
    DELETE TOP (1000000) FROM data.DtPerformace
    WHERE (DATEDIFF(hour, actualTimeStamp, @dtNow) > @ageHours)

    SET @rowCount=@@ROWCOUNT

    INSERT dbo.SSIS_Log VALUES
    ('Delete RealTime Data' ,'Delete SPROC Batch 2',GETDATE(),CONVERT(VARCHAR(10),@rowCount) + ' rows deleted.')
    END

    --Batch 3
    IF @rowCount = 1000000 -- if the last batch deleted < 1000000 rows there is no work to do...
    BEGIN
    DELETE TOP (1000000) FROM data.DtPerformance
    WHERE (DATEDIFF(hour, actualTimeStamp, @dtNow) > @ageHours)

    SET @rowCount=@@ROWCOUNT

    INSERT dbo.SSIS_Log VALUES
    ('Delete RealTime Data' ,'Delete SPROC Batch 3',GETDATE(),CONVERT(VARCHAR(10),@rowCount) + ' rows deleted.')
    END


    sami

    Wednesday, November 18, 2020 8:12 PM

All replies

  • Hi arc2013,
    This forum mainly discusses about Linq to sql. So I suggest you ask the question on the Microsoft Q&A forum, and you can get more professional answer.
    Thank you for your understanding.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.






    Thursday, November 19, 2020 1:59 AM