Archiving Data

Many times as a DBA I have taken over a system that has had data gathering for years.  The worst I took over had about 400 million records in the main table, with an average of 24 records in the secondary related table per primary record, data going back 12+ years.  In 2017, this might not be that bad, but back in 2002....

Seeing as the data had to be retrievable for any date, I could not simply delete the very old data.  These tables also had constant inserts and updates into them, so making sure the tables remained available became important, i.e. needed to have acceptable time that the table was being locked, with time for waiting transactions to finish.

The solution I came up with does this with variable size batches.  Now, with modern versions of SQL, there are other ways to do this, but the good thing about this method it works regardless of version of SQL, as well as edition.  Azure SQL DB would need some modification to make it work to archive to a separate database.

The following sample illustrates arching data from two tables, in a typical Sales header and detail tables, data consistency must be maintained.

CREATE OR ALTER PROCEDURE prc_ArchiveSalesData (@StartDate date, @EndDate date, @BatchSize INT)AS
BEGIN
   SET NOCOUNT ON
   SET XACT_ABORT ON
  
   DECLARE @rowcount INT-- set end date to be one after the date sent in to account for time data. 
-- Better performance than converting data in where clause
   SET @EndDate = DATEADD(DAY,1,@EndDate)
-- check for existing temp tables , drop and create
   IF OBJECT_ID('tempdb..#AllItems') IS NOT NULL
       DROP TABLE #AllItems
       CREATE TABLE #AllItems (
           SalesID INT
           )

   CREATE INDEX #idxAllItems ON #AllItems(SalesID) --lots of joins to happen on this

   IF OBJECT_ID('tempdb..#CurrentBatch') IS NOT NULL
       DROP TABLE #CurrentBatch

   CREATE TABLE #CurrentBatch (
       SalesID INT
       )

   CREATE INDEX #idx_Current ON #CurrentBatch(SalesID)
-- gather all items for the given date range
   INSERT INTO #AllItems
   SELECT  S.SalesID FROM Sale S WHERE S.SaleDate >= @StartDate AND S.SaleDate < @EndDate

   -- loop outside transaction, that way if a failure or need to cancel, will commit the succesful ones
   INSERT INTO #CurrentBatch
   SELECT TOP (@BatchSize) SalesID FROM #AllItems ORDER BY SalesID ASC -- technically no need to do this order by, but makes debugging easier
   SELECT @rowcount = @@ROWCOUNT
   WHILE @rowcount > 0
   BEGIN
       BEGIN TRANSACTION-- copy to archive tables
           INSERT INTO SalesArchive.dbo.Sale (SalesID, CustomerID, SalesPersonID, TotalSales, SaleDate)
           SELECT S.*  -- this would make archive fail if we change the table, which we would want
           FROM Sale S
           INNER JOIN #CurrentBatch CB
               ON S.SalesID = CB.SalesID
      
  
           INSERT INTO SalesArchive.dbo.SalesDetail ( SalesDetailID, SalesID, ItemID, Quantity)
           SELECT  SD.*
           FROM SalesDetail SD
           INNER JOIN #CurrentBatch CB
               ON SD.SalesID = CB.SalesID

-- delete from production tables

           DELETE FROM SalesDetail
           FROM SalesDetail SD
           INNER JOIN #CurrentBatch CB
               ON SD.SalesID = CB.SalesID

           DELETE FROM Sale
           FROM Sale S
           INNER JOIN #CurrentBatch CB
               ON S.SalesID = CB.SalesID
-- delete the current batch from all items
           DELETE FROM #AllItems
           FROM #AllItems AI
           INNER JOIN #CurrentBatch CB
               ON AI.SalesID = CB.SalesID
-- delete from current batch completed
           DELETE FROM #CurrentBatch
       COMMIT TRANSACTION       -- committing transaction here makes it possible to stop the archiving and not loose everything

       WAITFOR delay '00:00:10' -- 10 second pause before continuing, adjust as needed


       INSERT INTO #CurrentBatch
       SELECT TOP (@BatchSize) SalesID FROM #AllItems ORDER BY SalesID ASC -- technically no need to do this order by, but makes debugging easier
       SELECT @rowcount = @@ROWCOUNT

   END -- rowcount loop
END -- proc  

Hope you find this useful if you ever find a need for archiving large amounts of data.