When to archive data

Posted on 2005-04-19
Last Modified: 2010-03-19
Roughly how many records would need to be present in a table before you would considder archiving some of that data?

500,000 records?
1,000,000 records?
10 million?

I have a current process that inserts data into about 5 different tables.  Most of the tables have < 300K records, but there is one table with over 1 million records.  The over all process takes anywhere from 5 minutes to 3 hours to run (regaurdless of the number of records being loaded).

I'm currently running some SQL traces to see exactly what step is taking the longest (probably some locking issue), but I thought I'd ask about the archiving anyway.

Question by:_TAD_
    LVL 68

    Accepted Solution

    To me it's not so much about table size per se but about:

    1) what historic data you need available to meet business requirements

    2) what the system can handle *comfortably* (i.e. there's excess capacity available so the "excess" data isn't hurting the system anyway)

    Also naturally the quantity and types of requests against that table are also a big factor.  But, in a practical sense, once the business no longer needs to use it -- or only extremely rarely or in very rare cases -- you can archive it (or just delete it, depending on the data).
    LVL 7

    Assisted Solution

    i deal with about 2 million records (peanuts compared to a lot of people) i would never consider archiving anything.  Put your query's in QA and see what they are doing you will probally find the problem dosent stem from sheer amount of records.

    LVL 11

    Assisted Solution

    There comes a time when users no longer need the data for production purposes. This will give you an idea of what to archive and when. This is also the time when data moves from production/transactional needs over to historical/reporting use.

    A million records isn't a whole lot of records. If you find that you are drilling thru records that really aren't used (old) and removing the records would result in a significant performance improvement, then it's time to archive.

    Also, if think that backups are taking way too long and want to reduce the admin time, then it might be time to archive.
    LVL 23

    Author Comment


    Those were all pretty much my thoughts.  To sum up, the end users are experiencing some occasional performance issues.  Our VP, who is the original pointy-haired boss, heard that archiving data can have a great impact on performance.

    So, instead of really trying to figure out what's wrong, I have to chase after his ideas and either prove or disprove them.

    lucky me.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Suggested Solutions

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now