How can I optimize/make safe a script that deletes lots of data?

Question

I'm in the process of trying to develop a script that has to delete accidentally duplicated data (due to a bug in some code), from a client's production database.

It's quite a lot of data that I need to remove: 600,000 records in the primary table, plus similar volumes in a number of linked tables.

The script needs to run as quickly as possible, so I've done things like drop indexes and FKs before the script runs. It basically copies out the duplicated data into temporary (backup) tables, then deletes it from the original table(s). It runs through chunks of 10k records, using the view wrapped around the CTE. It then restores all the indexes and FKs at the end once no records are left in the view.

I've also switched to SIMPLE recovery mode before the script runs, but my db administrator asked me not to do this (if possible) on a production environment. If I leave this in FULL recovery mode the transaction log appears to grow wildly out of control.

The question is, how can I avoid the transaction log growing way out of control whilst this script runs? I plan on kicking all users off this database whilst the script runs, so we have no risk of losing new data during the run, should I need to restore. I need to have a backup of the database before I run the script and I need to have a backup point after the script has run. The changes to the data in between (as the script runs), seems to be irrelevant to me. The transaction log of that stuff could almost be thrown away (unless there is a reason why I might need it, but I can't think why?). I'm aware I can't use checkpoint anymore when I'm in full recovery mode. Should I shrink the transaction file after every run of the loop?

My script is below. I would welcome any suggestions in how I can improve the script (it is somewhat anonymized from the original), and most importantly, how I prevent myself from impacting the integrity of the data and current backups. This is live customer data, so it is really important I don't screw this up. I'm a programmer and not much of a DBA, so I can write the odd SQL script, but this is pushing the boundaries of my comfort zone!

-- #####################################################
-- full backup and restore point before you run this....
-- #####################################################

-- simple mode
ALTER DATABASE [Example.PROD] SET RECOVERY SIMPLE

-- Disable the indexes
ALTER INDEX [idx_09_23_04_148] ON [dbo].[Photo] DISABLE
ALTER INDEX [idx_09_23_04_615] ON [dbo].[Photo] DISABLE
ALTER INDEX [idx_09_23_04_617] ON [dbo].[Photo] DISABLE

-- drop all of the FK's
ALTER TABLE [dbo].[Photo] DROP CONSTRAINT FK_Photo_Tenant
ALTER TABLE [dbo].[Photo] DROP CONSTRAINT FK_Photo_DocumentCategory
ALTER TABLE [dbo].[Photo] DROP CONSTRAINT FK_Photo_DocumentFormat
ALTER TABLE [dbo].[Photo] DROP CONSTRAINT FK_Photo_StorageFile
ALTER TABLE [dbo].[Photo] DROP CONSTRAINT FK_Photo_StorageFile_Thumbnail
ALTER TABLE [dbo].[PhotoProxy] DROP CONSTRAINT FK_PhotoProxy_Tenant
ALTER TABLE [dbo].[PhotoProxy] DROP CONSTRAINT FK_PhotoProxy_Photo
ALTER TABLE [dbo].[CustomerPhoto] DROP CONSTRAINT FK_CustomerPhoto_Photo
ALTER TABLE [dbo].[CustomerPhotoProxy] DROP CONSTRAINT FK_CustomerPhotoProxy_PhotoProxy

-- let's create a view to speed up the execution plan on the deletes
-- drop and create
IF Object_ID('vw_Photo_TEMP') IS NOT NULL
    DROP VIEW vw_Photo_TEMP
GO

/*
This is going to constrain our view to all photos
that are considered duplicates
*/
CREATE VIEW vw_Photo_TEMP
AS
    WITH cte AS (
        SELECT DISTINCT
            p.PhotoId,
            p.TenantId,
            p.ClientIdentifier,
            p.DateCreated,
            p.StorageFileId,
            p.ThumbnailStorageFileId,
            ROW_NUMBER() OVER (PARTITION BY ClientIdentifier ORDER BY ( p.DateCreated ) DESC) AS RN
        FROM Photo p

        WHERE
            p.IsUploaded = 1
        AND
            p.ClientIdentifier IS NOT NULL
        AND
            p.DocumentFormatId = 1 
        AND
            p.CategoryId = 1
    )

    SELECT DISTINCT TOP 10000 * FROM cte
    WHERE  RN > 1 -- only ever expose the duplicates (newest are kept)
    ORDER BY ClientIdentifier, RN
GO

-- delete from 
DECLARE @msg NVARCHAR(1000)
DECLARE @Count INT, @MaxId INT, @MinId INT
DECLARE @db NVARCHAR(100) = 'Example.PROD'
DECLARE @Photos TABLE (PhotoId INT, StorageFileId INT, ThumbnailStorageFileId INT)
DECLARE @PhotoProxies TABLE (PhotoProxyId INT)

----  Caution : perform actual deletes now !!
--- start an Explicit transaction to avoid Log flushes.
SET NOCOUNT ON;
DECLARE @r INT;
SET @r = 1;

WHILE @r > 0
BEGIN
    BEGIN TRANSACTION;

    -- insert into table var
    INSERT INTO @Photos SELECT PhotoId, StorageFileId, ThumbnailStorageFileId FROM vw_Photo_TEMP

    -- first we need to copy the FK'ed relationships
    INSERT INTO CustomerPhoto_TEMP
        SELECT * FROM CustomerPhoto WHERE PhotoId IN (SELECT PhotoId FROM @Photos)

    INSERT INTO PhotoProxy_TEMP
        SELECT * FROM PhotoProxy WHERE PhotoId IN (SELECT PhotoId FROM @Photos)

    -- make sure we get all the proxies to delete too
    INSERT INTO @PhotoProxies SELECT PhotoProxyId FROM PhotoProxy WHERE PhotoId IN (SELECT PhotoId FROM @Photos)

    -- Now delete the related data
    DELETE FROM CustomerPhoto WHERE PhotoId IN (SELECT PhotoId FROM @Photos)
    DELETE FROM PhotoProxy WHERE PhotoId IN (SELECT PhotoId FROM @Photos)
    DELETE FROM CustomerPhotoProxy WHERE PhotoProxyId IN (SELECT PhotoProxyId FROM @PhotoProxies)

    SELECT @msg = 'Deleted proxy data:: ' + CONVERT(varchar, SYSDATETIME(), 121)
    RAISERROR(@msg, 0, 1) WITH NOWAIT

    -- Logically delete the main storage file, we can delete the physical file 
    -- then at some point in the future, when we have space issues.
    UPDATE StorageFile SET DELETED_FLAG = 1 WHERE StorageFileId IN (SELECT StorageFileId FROM @Photos)
    UPDATE StorageFile SET DELETED_FLAG = 1 WHERE StorageFileId IN (SELECT ThumbnailStorageFileId FROM @Photos)

    SELECT @msg = 'Deleted storage file data:: ' + CONVERT(varchar, SYSDATETIME(), 121)
    RAISERROR(@msg, 0, 1) WITH NOWAIT

    -- Copy the items we are about to delete into a backup table
    INSERT INTO Photo_TEMP
        SELECT * FROM Photo WHERE PhotoId IN (SELECT PhotoId FROM @Photos)

    -- Now we can safely delete the photo 
    DELETE FROM Photo WHERE PhotoId IN (SELECT PhotoId FROM @Photos)

    SELECT @msg = 'Deleted photo data:: ' + CONVERT(varchar, SYSDATETIME(), 121)
    RAISERROR(@msg, 0, 1) WITH NOWAIT

    SET @r = @@ROWCOUNT;

    -- reset table variable
    DELETE FROM @Photos
    DELETE FROM @PhotoProxies

    COMMIT TRANSACTION;
    SELECT @msg = 'Transaction committed:: ' + CONVERT(varchar, SYSDATETIME(), 121)
    RAISERROR(@msg, 0, 1) WITH NOWAIT

    -- CHECKPOINT;    -- if simple
    CHECKPOINT;

    SELECT @msg = RTRIM(instance_name) + ' (used in kb) ' + CAST(cntr_value AS NVARCHAR)
    FROM sys.dm_os_performance_counters 
    WHERE counter_name = 'Log File(s) Used Size (KB)'
    AND instance_name != '_Total'  
    AND instance_name = @db
    RAISERROR(@msg, 0, 1) WITH NOWAIT

END

-- recreate the FK's
ALTER TABLE [dbo].[Photo] ADD CONSTRAINT FK_Photo_Tenant FOREIGN KEY(TenantId) REFERENCES [dbo].[Tenant](TenantId)
ALTER TABLE [dbo].[Photo] ADD CONSTRAINT FK_Photo_DocumentCategory FOREIGN KEY(CategoryId) REFERENCES [dbo].[DocumentCategory](DocumentCategoryId)
ALTER TABLE [dbo].[Photo] ADD CONSTRAINT FK_Photo_DocumentFormat FOREIGN KEY(DocumentFormatId) REFERENCES [dbo].[DocumentFormat](DocumentFormatId)
ALTER TABLE [dbo].[Photo] ADD CONSTRAINT FK_Photo_StorageFile FOREIGN KEY(StorageFileId) REFERENCES [dbo].[StorageFile](StorageFileId)
ALTER TABLE [dbo].[Photo] ADD CONSTRAINT FK_Photo_StorageFile_Thumbnail FOREIGN KEY(ThumbnailStorageFileId) REFERENCES [dbo].[StorageFile](StorageFileId)
ALTER TABLE [dbo].[PhotoProxy] ADD CONSTRAINT FK_PhotoProxy_Tenant FOREIGN KEY(TenantId) REFERENCES [dbo].[Tenant](TenantId)
ALTER TABLE [dbo].[PhotoProxy] ADD CONSTRAINT FK_PhotoProxy_Photo FOREIGN KEY(PhotoId) REFERENCES [dbo].[Photo](PhotoId)
ALTER TABLE [dbo].[CustomerPhoto] ADD CONSTRAINT FK_CustomerPhoto_Photo FOREIGN KEY(PhotoId) REFERENCES [dbo].[Photo](PhotoId)

-- rebuild the indexes
ALTER INDEX [idx_09_23_04_148] ON [dbo].[Photo] REBUILD
ALTER INDEX [idx_09_23_04_615] ON [dbo].[Photo] REBUILD
ALTER INDEX [idx_09_23_04_617] ON [dbo].[Photo] REBUILD

-- back to full
ALTER DATABASE [Example.PROD] SET RECOVERY FULL

Summary of the questions:

1. Is switching to SIMPLE recovery mode a bad idea on a production database, even if there are no other writes to this database at the time this script runs? What damage could be done to existing backup chains?
2. If I remain in FULL recovery mode, how can I prevent the transaction log growing out of control?
3. At the end of each loop, is it worth truncating the transaction log, and/or backing it up?

Answer 1

There may be ways to simplify your script but no matter what you do you are going to be hitting the log while doing your deletes. The simplest answer is to run log backups more often during your script.

Let them know what you are doing and your concern. Obviously you've already talked to them about the issue or they wouldn't have asked you not to go to SIMPLE recovery. Ask that you be granted backup database permissions and for a script to run log backups. Then put a log backup after each of your deletes/updates. That will clear the log and make it ready for the next one.

If your database admin doesn't want you to add log backups to your script you can try breaking up your transactions so they aren't hitting your log all in one chunk. Something like this:

WHILE @@rowcount > 0
    DELETE TOP (50000) FROM [tablename] WHERE conditions;

Then ask your admin to run log backups fairly rapidly during that period .. say every couple of minutes.

---

Edit: Explanation why the more frequent backups will help with the log growth: Start with a brief difference of SIMPLE and FULL recovery.

• SIMPLE - Space in the log can be marked for re-use without a log backup.
• FULL - Space in the log can not be marked for re-use without a log backup.

Now there are a number of other things in play here. If you have replication, mirroring etc in place then those will also play a factor in when the log space will be re-used. At it simplest though, with nothing else going on, a log backup will allow space in the log to be re-used.

So in your case when you are converting to SIMPLE the log space gets freed up as soon as your transaction completes. In FULL recovery the log space can't be freed up until both the transaction is complete and a log backup has occurred. That means that if you take log backups on a regular basis through your script (or frequently enough on a timed schedule) then you will be able to re-use enough of the log space to avoid explosive growth.