Showing posts with label interval. Show all posts
Showing posts with label interval. Show all posts

Friday, March 23, 2012

ReportServerTempDB log full regularly

My ReportServerTempDB is full regularly .I had to shirink the database on regular interval though i schedule for shirinking on 30 minutes.But as soon there is any exception due to scripting the log is full and my all report manaer throws error.

After shirinking the ReportServerTempDB the reports starts working fine.

Please Help me to remove this frequent error.

It's more than likely the chunkdata table. That table stores the session cache and cached instances of your reports. The information is stored in an intermediary format (pre-rendering) which means that it contains data, too.

Each time a report is run with a different query parameter, a new cached instance is created. That can add up and quickly grow your ReportServerTempDb database.

One technique to minimize this is to create a rather generic query that returns a superset of the data needed so that only one copy of the report is cached. You can then apply filters to that cached instance.

You can also adjust the cache expiration policies.

There's good document on SSRS scalability here.
download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/rs_scalability.doc

HTH...

Joe

ReportServerTempDB log full regularly

My ReportServerTempDB is full regularly .I had to shirink the database on regular interval though i schedule for shirinking on 30 minutes.But as soon there is any exception due to scripting the log is full and my all report manaer throws error.

After shirinking the ReportServerTempDB the reports starts working fine.

Please Help me to remove this frequent error.

It's more than likely the chunkdata table. That table stores the session cache and cached instances of your reports. The information is stored in an intermediary format (pre-rendering) which means that it contains data, too.

Each time a report is run with a different query parameter, a new cached instance is created. That can add up and quickly grow your ReportServerTempDb database.

One technique to minimize this is to create a rather generic query that returns a superset of the data needed so that only one copy of the report is cached. You can then apply filters to that cached instance.

You can also adjust the cache expiration policies.

There's good document on SSRS scalability here.
download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/rs_scalability.doc

HTH...

Joe

Wednesday, March 21, 2012

ReportServer DB and ReportServerTempDB databases

Hi all,

Is it possible to cleanup ReportServer DB and ReportServerTempDB by setting the time interval ? For example, I want data in these two dbs to be deleted automatically after 5 or 10 minutes. How can I do that because as new reports are added and executed data goes on increasing in very short time and hurts performance.

Thanks,

Prashant

What data specifically are you trying to clean up? How are you measuring performance to know that this is the root of your problem?|||

Thanks for the reply !

No, there is no problem till now, but the data which is stored in tables like ChunkData, SnapShotData etc. is quite huge as many reports are running. I know, its temporary but is there any way from where I can set the time limit after which it will be cleaned up ?

Also one more thing, if I delete a report, what would happen to data stored in above tables ? Does it (data) get deleted with the deletion of report which populated them or does it remain in tables to be deleted after time limit ?

Thank again,

Prashant

|||

You need CleanupCycleMinutes in rsReportServer.config -- The default is 10 minutes.

<don't get me wrong here...>

It doesn't really sound like you know whether large orphaned snapshots are a perf problem in your scenario...by definition, they shouldn't be, as they are just blobs sitting around doing nothing. Even if you clean up more often, there's no win (unless you are storing snapshots to the filesystem with limited space) as I don't think the databases themselves are going to shrink for you anyway...You could actually make a situation which you think is bad worse by modifying this setting, as there's a cost to running the cleanup process. You don't want it firing all the time, and when most people modify it, the actually bump the value UP to make sure they don't get contention between the "normal" SSRS processes that read/write to these tables and the "cleanup" process.

</don't get me wrong here...>

Hope this helps?

|||

Thanks for opening my eyes ! Now I prefer to leave this cleaning task untouched and let RS take care of it.

Thanks again, Russell

Prashant

sql