August 26, 2009 at 5:29 am
Hello
Some advice required if you have a moment.
Ever since installing the reporting services product, our reportserver database increases by 100-120mb per day. It is now heading fast for 40gb. That may not be large to some people but causes issues with keeping backups online and copying to dr sites.
Does anyone implement an 'archiving of old reports' strategy in their environment? Or can anyone offer advice on the best way to manage the size of the database?
Should reports be archived to a filesystem and deleted from the database? Or should the database be changed every year (reportserver2009, reportserver2010 etc)?
We are running sql2005 sp2 (soon to go to sp3).
Thank you
William Burke
August 26, 2009 at 4:00 pm
which tables are growing? Do you keep a lot of report snaphots? Or cached copies?
August 26, 2009 at 4:21 pm
Does anyone implement an 'archiving of old reports' strategy in their environment? Or can anyone offer advice on the best way to manage the size of the database?
Should reports be archived to a filesystem and deleted from the database? Or should the database be changed every year (reportserver2009, reportserver2010 etc)?
The first question are you running any code through it because it is not a user database, the only reason it is growing is if you are creating a lot of reports with images and creating many subscriptions including data driven subscriptions.
And no you cannot use the file system for it but you need to run full recovery backup for ReportserverDB and and simple recovery backup for ReportserverTempDB. You need both to upgrade and recover from hardware failure.
Kind regards,
Gift Peddie
August 27, 2009 at 8:27 am
Thanks for the replies so far.
This is really a question for people who have dealt with archiving old reports before.
Basically we produce lots of reports every evening and they are stored in that database. The reports will have to be kept for seven years. But we cannot have a database of over 200gb in size. So what do people do?
The reports over one year old still have to be accessible but where can they go.
Only save them to documents in the filesystem that are searched for and printed off when required.
Or can another 'reportserver' type database be created to hold the older reports?
August 27, 2009 at 8:47 am
This is really a question for people who have dealt with archiving old reports before.
Basically we produce lots of reports every evening and they are stored in that database. The reports will have to be kept for seven years. But we cannot have a database of over 200gb in size. So what do people do?
There is something very wrong with that because if you create reports that runs from a separate database you only need a DR copy of the datasource database and your reports folder in report manager to run the reports, so you just need an archive section of reportmanager with folders that only admin can access. What you have now is making all your operation ReportServerDB and ReportServerTempDB dependent, that is not a viable solution.
I think you need a Reporting Services architecture consultant that can refactor your current implementation.
Kind regards,
Gift Peddie
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply