Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4

    Unanswered: [MS Reporting Server] Archive/delete data from database

    Due to a lack of monitoring and limitation of input, my Reporting Server database has grown beyond managing proportions.
    I know how to limit the input by reducing the number of active rules. What I don't know is how to delete or move older data (older than 1 year) from the current database.

    Can anyone point me to a document/howto, or tell me how to do this?
    I'm not crazy, I'm an aeroplane!

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You need at least a column with a Year, Date or Datetime that you can use to select which records must stay and which records must be archived.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by Wim View Post
    You need at least a column with a Year, Date or Datetime that you can use to select which records must stay and which records must be archived.
    That means I have to go through every table and see if it has a date/time/year-column and manually delete the data? Isn't there a manual that describes this procedure? I'm sur I am not the first or only one that needs archiving/purging.
    I'm not crazy, I'm an aeroplane!

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You are definitely not the first who is faced with archiving/purging.

    But if it is impossible for you to identify which records need to be archived and which records can stay in the main database, than it's ... impossible to archive (part of) your database.

    But I am sure there will be at least 1 column in your database that you can use for that purpose, like "aangiftejaar" (I don't know the English word for it "declaration year"?), BillingDate, SchoolYear, ...

    I'll describe how we do it. I'm not saying this is the best way, but it works for us. It is a manual process, as it only needs to run once a year, we haven't automated it.

    Suppose the name of your database is MyDatabase.
    A) things you only need to do once
    - Create a second database with the name MyDatabase_Archive.
    - Create all the tables in MyDatabase_Archive that you want to archive, with the same table structure as in MyDatabase.
    You will have to decide which tables to archive and which not. And you will need (a) column(s) that can be used to decide if the record must be archived or not. With JOINs you can use a column in one table to decide if a record in another table must be archived.
    We don't archive our Person table or Customer table, even when they haven't ordered anything in years. But we do archive all paid bills with a billing date older than a certain date (like 1st Jan 2010). But your mileage may vary. It depends on your business and you will have to use some common sense.

    B) things you need to do each year (also check for new tables that can be archived)
    - Backup MyDatabase
    - Backup MyDatabase_Archive
    - Exclude the users from accessing the database

    C) Things to do for each table that has to be archived
    - Copy the records from MyDatabase to the same table in MyDatabase_Archive.
    - Check if the records are in MyDatabase_Archive
    - Delete the records in MyDatabase. If you have defined FK's, the order of the deletes matters.

    As the tables may change from year to year, we also check that the table structure (columns, data types, lengths) in MyDatabase_Archive is the same as the one in MyDatabase, by using MSSQL's system tables.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Thank you/bedankt,

    I can work with this
    I'm not crazy, I'm an aeroplane!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •