0

I have a EU privacy requirement to delete production data regularly. The script runs weekly when the traffic is quiet. I wondered if there is any way to improve this script. I can't delete directly from the view because it joins on a couple of tables. Customer and address data can be shared across orders. If the customer record and address records aren't used within a given time period we have to delete them(i.e. one year old so this isn't a truncate of the entire table). This will be a stored procedure that runs as a job, but for simplicity I've shown it here without the sproc definition.

DECLARE @TimeoutMinutes INT = 60 -- SPROC Variable

-- make sure we raise runtime errors
SET XACT_ABORT ON;
SET NOCOUNT ON;

DECLARE @msg NVARCHAR(1000)
DECLARE @BeginDateTime DATETIME, @CommitDateTime DATETIME, @TransactionTimeout DATETIME;
DECLARE @r INT, @MaxId INT, @MinId INT, @TransactionCount INT, @TotalDeleteCount INT = 0;
DECLARE @Orders TABLE (Id INT, CustomerId INT, ShippingAddressId INT, CustomerAddressId INT);

SELECT @msg = CONVERT(varchar, SYSDATETIME(), 121) + ' :: ' + 'Begin Order Deletion'
RAISERROR(@msg, 0, 1) WITH NOWAIT

SELECT @r = COUNT(*) FROM vw_OrdersToDelete -- list of orders older than 1 year old

SELECT @msg = CONVERT(varchar, SYSDATETIME(), 121) + ' :: ' + 'Total Rows to Delete: ' + CAST(@r AS NVARCHAR(10))
RAISERROR(@msg, 0, 1) WITH NOWAIT

-- Set a time limit of @TimeoutMinutes minutes for the deletion. If not all the data has been deleted by then stop anyway
SET @TransactionTimeout = DATEADD(mi, @TimeoutMinutes, GETDATE())

WHILE @TransactionTimeout > GETDATE() AND @r > 0
BEGIN
    BEGIN TRY

        BEGIN TRANSACTION;

        -- disable all constraints
        EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

        -- Data for linked deletes
        INSERT INTO @Orders SELECT top 10000 OrderId, Order.CustomerId, ShippingAddressId, Customer.AddressId FROM vw_OrdersToDelete INNER JOIN Customer ON Customer.CustomerId = Order.CustomerId
        SELECT @MinId = MIN(Id) FROM @Orders
        SELECT @MaxId = MAX(Id) FROM @Orders

        SELECT * FROM @Orders

        SELECT @msg = CONVERT(varchar, SYSDATETIME(), 121) + ' :: ' + 'OrderIDs: Min: ' + CAST(@MinId AS NVARCHAR(10)) + ' & Max: ' + CAST(@MaxId AS NVARCHAR(10))
        RAISERROR(@msg, 0, 1) WITH NOWAIT

        -- deleted related data
        DELETE FROM OrderLine WHERE OrderId IN (SELECT Id FROM @Orders)
        DELETE FROM OrderEvent WHERE OrderId IN (SELECT Id FROM @Orders)

        -- delete orders
        DELETE FROM [Order] WHERE OrderId IN (SELECT Id FROM @Orders)

        SET @TransactionCount = @@rowcount
        SET @r = @r - @TransactionCount
        SET @TotalDeleteCount = @TotalDeleteCount + @TransactionCount

        -- since customers are shared across order, we don't want to delete 
        -- the customer if it has a newer order, because it will fail
        DELETE FROM Customer
        WHERE CustomerId IN (SELECT CustomerId FROM @Orders) AND CustomerId NOT IN (
            SELECT CustomerId FROM [Order] WHERE CustomerId IN (SELECT CustomerId FROM @Orders)
        )

        -- same goes for addresses. We don't want to delete them if the shipping address or billing address has been reused
        DELETE FROM [Address]
        WHERE AddressId IN (SELECT ShippingAddressId FROM @Orders) AND AddressId NOT IN (
            SELECT ShippingAddressId FROM [Order] WHERE ShippingAddressId IN (SELECT ShippingAddressId FROM @Orders)
        ) AND AddressId IN (SELECT CustomerAddressId FROM @Orders) AND AddressId NOT IN (
            SELECT AddressId FROM Customer WHERE AddressId IN (SELECT CustomerAddressId FROM @Orders)
        )

        -- enable all constraints
        EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

        -- Commit the transaction    
        COMMIT TRANSACTION;

        -- Clear the orders table    
        DELETE FROM @Orders

        SELECT @msg = CONVERT(varchar, SYSDATETIME(), 121) + ' :: ' + 'Rows Remaining: ' + CAST(@r AS NVARCHAR(10))
        RAISERROR(@msg, 0, 1) WITH NOWAIT

    END TRY
    BEGIN CATCH

        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );

        -- If >= SQL 2012 replace all code in catch block above with
        -- THROW;

        WHILE @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

    END CATCH

END

SELECT @msg = CHAR(13) + CONVERT(varchar, SYSDATETIME(), 121) + ' :: ' + 'Delete Transaction Completed'
RAISERROR(@msg, 0, 1) WITH NOWAIT

SELECT @msg = CONVERT(varchar, SYSDATETIME(), 121) + ' :: ' + 'Rows Deleted: ' + CAST(@TotalDeleteCount AS NVARCHAR(10))
RAISERROR(@msg, 0, 1) WITH NOWAIT

I'd welcome any suggestions to how to improve this script with the following in mind:

  • This is a pretty large database (table names and "customer orders" concept are theoretical to illustrate the point)
  • Database is in FULL recovery mode and has backup sets configured which I don't want to break
  • I want the script to be resilient if it fails for any reason
  • It needs to run for a limited amount of time before exiting in order not to affect users in key hours
  • I'm aware of using CHECKPOINT in SIMPLE recovery mode to aid with freeing up the transaction log, but I don't think it is viable to switch the entire database into SIMPLE mode and back again to FULL (correct me please if I'm wrong and this is completely safe)
  • Is it a bad idea to declare the table variable outside the loop? Does it make a difference?
  • Can I do the constraints drop / re-enable once outside the transaction safely?
  • Maybe creating indexes on the table variables and using joins on them for the deletes instead of the IN predicates. Just a guess, you'd have to test. If you do that, please, tell the outcome. – Ronaldo Apr 23 at 23:56
  • 1
  • It is safe to switch from full to simple mode and back again. You will just need to do a full backup immediately after switching it back to full. This is not an uncommon practice. You'll of course lose point-in-time recovery during the time it's in simple mode, though. – Tony Hinkle May 12 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.