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?

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.

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.

Thank you/bedankt,

I can work with this