CODEWITHSUNDEEP
sql-serverazureazure-runbook
myname
myname

Reputation: 41

How to delete millions of rows from an Azure database?

The problem I am facing now is that I have to delete over 90 million rows from an Azure Microsoft SQL database, and I cannot simply remove the table and rebuild a new one.

And that is the code I am using

DECLARE @NRO INT = 0; 
WHILE 1 = 1
BEGIN
    SET @NRO = @NRO + 1; 
    PRINT CONCAT(@NRO * 2000, ' ROWS DELETED.')

    DELETE TOP(2000)
    FROM AuditDetail
    WHERE Id < 25000000;

    IF @@ROWCOUNT < 2000 
        BREAK;
END

I have tried to use the Azure Runbook to finish the job, but it can only allow me to run the SQL script for 9 hours, even I set the timeout time to more than 24 hours.

My question: how to setup the Runbook correctly to run the script for as long as I wish, or is there the other way to finish the job? I have tried to use the Checkpoint, but it did not work, I may do it wrong :(

Thank you.

Upvotes: 1

Views: 1268

Answers (1)

Rob Reagan
Rob Reagan

Reputation: 7686

You're on the right track given your constraints.

I'd also recommend playing around with your batch size while monitoring the relevant metrics in your Azure SQL metrics blade. If your batch size of 2k records at a time isn't even making a blip on CPU or memory, consider stopping the query and increasing the batch size.

You might also consider a scale up depending on your SKU. If you're on the vCore or Hyperscale SKUs, you will be able to speed things up by scaling out your number of cores, then scaling them down when all delete ops are finished.

One more thing to consider if you're really risk-adverse, there are no legal hurdles, and you have budget to do so. You can create a copy of your prod database to experiment on. It's as simple as connecting to the master database and executing "Create database X as copy of Y". Doing so doesn't impact your database's performance. Then you can experiment on this copy of prod.

Upvotes: 2

Related Questions