Recovering space on a restored and pruned SQL Server database for testing purposes



  • I have a requirement to regularly take a backup of a set of tenant databases (all same schema), and restore them in our UAT environment, then clear down most of the client data, leaving only a few months worth. I'm clearing about 7 years of data each time. This happens monthly. Each database is about 100GB. SQL Server 2008 R2.

    My current process is this:

    • Backup PROD to a BAK file full copy-only
    • Backup the current UAT database full copy-only
    • Restore the PROD BAK file with replace over the UAT database
    • Then I run a script that is structured much like the following, which deletes data in batches from a rolling view:

    Script:

    USE [UAT.TenantName]
    

    -- This is setup to keep only the orders
    -- created within the last three months.
    -- Change as appropriate for the requirements
    CREATE VIEW vw_DeleteOrder
    AS
    SELECT TOP (10000) * FROM [Order]
    WHERE DateCreated < DATEADD(MONTH,-3, GETDATE())
    ORDER BY OrderId DESC
    GO

    -- protect against ever running this against the wrong database
    -- database should have the name UAT as prefix
    IF (DB_NAME() LIKE 'UAT%')
    BEGIN

    DECLARE
    @DatabaseName NVARCHAR(30) = '[' + DB_NAME() + ']',
    @RestrictedUser BIT = 0,
    @DisableConstraints BIT = 0,
    @Reindex BIT = 0
    
    IF @RestrictedUser = 1
    BEGIN
        EXEC('ALTER DATABASE ' +  @DatabaseName + ' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE; ')
    END
    
    IF @SimpleMode = 1
    BEGIN
        EXEC('ALTER DATABASE ' +  @DatabaseName + ' SET RECOVERY SIMPLE; ')
    END
    
    DECLARE @msg NVARCHAR(1000)
    DECLARE @Ordercount INT, @MaxId INT, @MinId INT
    
    IF @DisableConstraints = 1
    BEGIN
        -- disable all constraints
        EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    END
    
    -- notifications and messaging (not relevant between envs)
    DELETE FROM OrderNotificationQueue
    DELETE FROM OrderMessageQueue
    DELETE FROM PaymentExtractOrderPayment
    DELETE FROM PaymentExtract
    
    SELECT @msg = 'Deleted Notifications and messaging:: ' + CONVERT(varchar, SYSDATETIME(), 121)
    RAISERROR(@msg, 0, 1) WITH NOWAIT
    
    DECLARE @OrderIds TABLE (Id INT, CustomerId INT, ShippingAddressId INT)
    DECLARE @AttachmentIds TABLE(Id INT)
    DECLARE @OrderAttachmentIds TABLE (Id INT)
    DECLARE @AddressIds TABLE (Id INT)
    DECLARE @CustomerIds TABLE (Id INT)
    DECLARE @StorageFileIds TABLE (Id INT)
    DECLARE @OrderInvoiceIds TABLE (Id INT)
    
    SET NOCOUNT ON;
    DECLARE @r INT;
    SET @r = 1;
    
    WHILE @r > 0
    BEGIN
        BEGIN TRY
    
        BEGIN TRANSACTION;
    
    
        -- Data for linked deletes
        INSERT INTO @OrderIds SELECT OrderId, CustomerId, ShippingAddressId FROM Order WHERE OrderId IN (SELECT OrderId FROM vw_DeleteOrder)
        INSERT INTO @OrderAttachmentIds SELECT OrderAttachmentId FROM OrderAttachment WHERE OrderId IN (SELECT Id FROM @OrderIds)
        INSERT INTO @CustomerIds SELECT CustomerId FROM Order WHERE OrderId IN (SELECT Id FROM @OrderIds)
        INSERT INTO @AddressIds SELECT AddressId FROM Customer WHERE CustomerId IN (SELECT CustomerId FROM @OrderIds)
        INSERT INTO @AddressIds SELECT ShippingAddressId FROM @OrderIds
        INSERT INTO @StorageFileIds SELECT StorageFileId FROM OrderAttachment WHERE OrderId IN (SELECT Id FROM @OrderIds) AND StorageFileId IS NOT NULL
        INSERT INTO @StorageFileIds SELECT StorageFileId FROM OrderInvoice WHERE OrderId IN (SELECT Id FROM @OrderIds) AND StorageFileId IS NOT NULL
        INSERT INTO @OrderInvoiceIds SELECT OrderInvoiceId FROM OrderInvoice WHERE OrderId IN (SELECT Id FROM @OrderIds)
             
        SELECT @msg = 'Completed inserts:: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT
           
        -- Invoicing        
        DELETE FROM OrderInvoiceQueue WHERE OrderId IN (SELECT Id FROM @OrderIds)
        DELETE FROM OrderInvoiceAppliedRate WHERE OrderInvoiceId IN (SELECT Id FROM @OrderInvoiceIds)
        DELETE FROM OrderInvoiceLine WHERE OrderInvoiceId IN (SELECT Id FROM @OrderInvoiceIds)
        DELETE FROM OrderInvoice WHERE OrderId IN (SELECT Id FROM @OrderIds)
        SELECT @msg = 'Deleted Order Invoice data:: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT          
      
        -- Order data
        DELETE FROM OrderAttribute WHERE OrderId IN (SELECT Id FROM @OrderIds)
        DELETE FROM OrderPayment WHERE OrderId IN (SELECT Id FROM @OrderIds)
        DELETE FROM OrderNote WHERE OrderId IN (SELECT Id FROM @OrderIds)
        DELETE FROM OrderReference WHERE OrderId IN (SELECT Id FROM @OrderIds)
        DELETE FROM OrderOwner WHERE OrderId IN (SELECT Id FROM @OrderIds)
        DELETE FROM OrderRelation WHERE OrderId IN (SELECT Id FROM @OrderIds) OR RelatedOrderId IN (SELECT Id FROM @OrderIds)
        
        SELECT @msg = 'Deleted Order related data:: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT
    
        -- Order attachment data
        DELETE FROM OrderAttachment WHERE OrderId IN (SELECT Id FROM @OrderIds)
    
        SELECT @msg = 'Deleted Order attachment data:: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT
    
        -- Random links to Order
        DELETE FROM FinanceExtractOrderInvoice WHERE OrderInvoiceId IN (SELECT Id FROM @OrderInvoiceIds)
    
        -- Storage files
        DELETE FROM StorageFile WHERE StorageFileId IN (SELECT Id FROM @StorageFileIds)
              
        DELETE FROM vw_DeleteOrder;
         
        SET @r = @@ROWCOUNT;
        SELECT @msg = 'Rows left:: ' + CAST(@r AS NVARCHAR(10)) + ' :: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT
         
        SELECT @msg = 'Deleted Order data:: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT
    
        -- Delete customers from defined list (need to check multiple use addresses)
        DELETE FROM Customer 
        WHERE CustomerId IN (SELECT DISTINCT CustomerId FROM @CustomerIds) 
        AND CustomerId NOT IN (SELECT CustomerId FROM [Order])
        SELECT @msg = 'Deleted customer data:: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT
          
        -- Delete addresses from defined list (need to check multiple use customers)
        DELETE FROM [Address] 
        WHERE AddressId IN (SELECT DISTINCT Id FROM @AddressIds)
        AND AddressId NOT IN (SELECT ShippingAddressId FROM [Order])
        AND AddressId NOT IN (SELECT AddressId FROM Customer WHERE CustomerId IN (SELECT CustomerId FROM [Order]))
        SELECT @msg = 'Deleted address data:: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT
    
        IF @@TRANCOUNT = 1 
        BEGIN
            COMMIT TRANSACTION
            SELECT @msg = 'Transaction committed:: ' + CONVERT(varchar, SYSDATETIME(), 121)
            RAISERROR(@msg, 0, 1) WITH NOWAIT
        END
    
        --- run twice to make sure log wraps
        CHECKPOINT 
        CHECKPOINT
        SELECT @msg = 'Checkpoint:: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT
        
        END TRY
        BEGIN CATCH
            IF (XACT_STATE()) = -1
                ROLLBACK TRANSACTION
            IF (XACT_STATE()) = 1
                COMMIT TRANSACTION
    
            -- stop loop
            SET @r = 0
    
            DECLARE 
                @Error NVARCHAR(MAX) = ERROR_MESSAGE(), 
                @Severity INT = ERROR_SEVERITY(),
                @State SMALLINT = ERROR_STATE()
            RAISERROR(@Error, @Severity, @State)
        END CATCH
    
    END
    
    IF @DisableConstraints = 1
    BEGIN
        EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' 
    END
    
    SELECT @Ordercount = COUNT(*) FROM [Order]
    SELECT @msg = 'Final Orders:: ' + CAST(@Ordercount AS NVARCHAR(10)) + ' :: ' + CONVERT(varchar, SYSDATETIME(), 121)
    RAISERROR(@msg, 0, 1) WITH NOWAIT
    
    IF (@RestrictedUser = 1)
    BEGIN
        EXEC('ALTER DATABASE ' +  @DatabaseName + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE; ') 
    END
    
    IF @Reindex = 1
    BEGIN
        ---- Rebuild affected indexes
        DBCC DBREINDEX ('Address')
        DBCC DBREINDEX ('Order')
        -- etc
    END
    
    EXEC('ALTER DATABASE ' +  @DatabaseName + ' SET RECOVERY FULL; ')
    

    END
    ELSE
    BEGIN
    RAISERROR('Database clear down process only runs when the database name has the prefix UAT', 0, 1) WITH NOWAIT
    END

    • I then anonymize what is left for GDPR purposes (overwriting customer GDPR related data with junk. i.e. Customer names, addresses, ad other personably identifiable information), so that tenants can do operational business process testing.

    At the end of this process 98% of the data has gone, but the database size is still the same on disk.

    https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/ screams "don't use shrink database or shrink file". However, these databases still need to be operational, but I really want to recover the space.

    For this kind of scenario, where I need to copy and restore a database like this, and recover space without making the database horrifically fragmented, and I really really don't care about recovering the data I've deleted in this process, what is the suggested solution?



  • Your options are:

    • Backup/restore and then shrink
    • Export/import only the data that you actually need (instead of backup/restore)

    You have to ask yourself: What is this disk space worth?

    Also, if this is a regular process, you will need that disk space the next time you perform the operation - so what do you really gain by doing the shrink?

    If you are still determined to do the shrink, then just go ahead. You'll have a horrible fragmentation after the shrink, but unless you have the database on a single magnetic disk (very unlikely) that might not hurt you much - at all!

    If you still want to get rid of the fragmentation, then just rebuild your indexes - the database is tiny now that you have deleted all this data.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2