0

I am still an amateur when it comes to MS SQL Server.

I have been tasked with Archiving our Production Database which is growing daily. Mainly I am looking at the transactions tables, simply because they are the ones that generate the most data (average of a million records per day).

I understand that the tables from which they pull data from might need to be archived as well for referential integrity purposes.

I also need a way to be able to query this archived data whenever needed as quickly as possible if there's a report needed. Any advice?

  • 1
    If the data has to still be online and available, you are basically looking at a new db...same server, different server, StretchDB in Azure, etc. Why is leaving it in place not acceptable, ie...why archive? – Kevin3NF Nov 23 '16 at 15:14
2

It sounds like whatever application is feeding this database is dumping tons of records in there if as you say there are 1 million transactions a day. See if whatever is feeding this database has a retention feature. If you have 1 million + records a day you are going to run out of space quickly.

There are a lot of factors involved in this type of process. Size of the database, number of transactions, network speeds, storage space, etc...

If I were in your shoes at this moment I would restore a copy of your backup either to another instance or to the same instance if you have room. I would truncate out all data that is not needed. I would then create a SQL agent job that will insert the data into the new database tables with the GetDate() function and whatever offset time is needed and do it in a batch function of maybe 1000 per batch.

Here is a sample of one that I use. It can be an insert or delete batch:

SET NOCOUNT ON;
DECLARE @r INT;
SET @r = 1;
WHILE @r > 0
    BEGIN
        BEGIN TRANSACTION;
        --This could be an INSERT or DELETE etc.
        DELETE TOP (1000)
        FROM dbo.application_log
        WHERE(DATEADD(MM, -6, GETDATE()) > date_created);
        SET @r = @@ROWCOUNT;
        COMMIT TRANSACTION;
    END;

After that is completed, then run a delete batch to remove the data from the primary database, which will purge the old data.

Keep in mind this is a very simplistic model of a data transfer and purge. There are many ways to accomplish this, but require a greater knowledge of SQL and would take much longer to explain than we have time for here.

Hope that helps.

  • That's a basic idea for you. Good Luck! – bwilliamson Nov 26 '16 at 3:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.