Batched Data Archival

Kevin Feasel

2017-07-03

Data, T-SQL

Anders Pedersen has a script to archive data in smaller batches:

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.

Click through for the script.

Related Posts

Diving Into The Data Lake

Jesse Gorter explains the data lake metaphor: A data lake is a concept that opposes the idea of a data mart. Where a data mart is a silo with structured and cleansed data, a data lake is a huge data collection that is unstructured and raw. You could also say that a data mart is […]

Read More

Reading Drive Files Recursively From T-SQL

Slava Murygin shows how to use a common table expression to read a recursive drive listing: In this blog I’ll use undocumented in SQL Server extended stored procedure “xp_dirtree“. That procedure is well described in Patrick Keisler’s blog. Briefly, “xp_dirtree” extended procedure returns tree of sub-directories from given folder and has three parameters: 1. Starting or root folder […]

Read More

Categories

July 2017
MTWTFSS
« Jun  
 12
3456789
10111213141516
17181920212223
24252627282930
31