About myself and everyone else.
I’ve been doing a little work recently todo with SQL archiving.
As with any tools you might build as a developer for internal use you have to follow a few guidelines. Well you don’t have to but generally a tool is only as good when someone other than yourself is using it!
So when you design tools keep in mind.
My tool… the one for archiving.
I’ve created a narrow table with some basic info.
if object_id(‘archive_configuration’) is not null drop table archive_configuration
create table archive_configuration(
config_id int identity(1,1),
enabled bit default 0,
last_run_date datetime default getdate(),
As you can see from this it’s pretty basic, there are a few things to point out. The query bit is you delete statement which you should make dynamic sql. So just replace ‘ with ” . The batch total is how many rows in total you want to delete before stopping. The size is how many rows to delete at t a time. they delay is used with the waitfor statement.
The batch size and delay should be configured based on your systems contention.
Since I’ve written a report over this table I can see the tech stuff but also publish a description on the rules of why I do the delete in a specific way. This might be something like ‘deleting order notes for orders older than 1 month’
Note: I’m saying deleting but this is because it’s deleted on the source, the term move would probably be more accurate since I’m moving the data from database A which should be nice and streamlined to database B which can be slow and on cheaper hardware. I’m using the delete and output clause to achieve this.
I then have some logging info which again comes in handy in the report since I can see when things are not as expected.
Now that my config table is setup I can stick some values in it.
Insert into archive_configuration(query,batch_total,batch_size,batch_delay,[description],[enabled])
select ‘Delete from a
output deleted.*,getdate() into archive..table1
where date_sent is not null’,
‘this is a test example for archive, it does a straight delete based on an isnull condition’,
I’ve inserted some presets.
This will be read out of the table based in a config id which is where the identity column comes in. The process archive proc then wraps all the presets and variables and executes in a transaction in a while loop.
I’ve also added a noddy error handling proc in order to get some info when it fails.
Since the query is well… free text and you can really write some nice logic in here with joins for archiving relational data etc. I think it’s pretty flexible. You can also handle all the errors and keep track of run statuses and a little bit of the business rules.
The code can be found below