Home / Reporting Services / How to Refresh Reporting Services Cache?
Dynamic Refresh SSRS Cache Using SQL

How to Refresh Reporting Services Cache?


Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /home/kraije21/domains/sqlblog.nl/public_html/wp-content/plugins/seo-auto-links/seo-auto-links.php on line 76

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /home/kraije21/domains/sqlblog.nl/public_html/wp-content/plugins/seo-auto-links/seo-auto-links.php on line 246

Check Also

Cannot truncate table because it is being referenced by a FOREIGN KEY constraint


Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /home/kraije21/domains/sqlblog.nl/public_html/wp-content/plugins/seo-auto-links/seo-auto-links.php on line 76

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /home/kraije21/domains/sqlblog.nl/public_html/wp-content/plugins/seo-auto-links/seo-auto-links.php on line 246

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /home/kraije21/domains/sqlblog.nl/public_html/wp-content/plugins/seo-auto-links/seo-auto-links.php on line 76

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /home/kraije21/domains/sqlblog.nl/public_html/wp-content/plugins/seo-auto-links/seo-auto-links.php on line 246

10 comments

  1. Thanks for your blog post which shines a lot of light on this very effective (but poorly-documented) functionality in SSRS.

    I also noticed that deploying a report from Visual Studio also clears all caches for that report.

  2. Ronald Kraijesteijn

    Thanks Mike. There is hardly NO documentation available about this subject. It was a nice job to discover how everything works under water. I hope this article helps you to get things done!

  3. Thanks a lot for putting your effort into a blog. It helped a lot 🙂 Your solution is working great.
    I have no idea why such a basic feature like cache Invalidation/refresh cannot be triggered easily by standard.
    In addition to your “NULL delivery provider” I’m using your method successfully for cache refresh plans and I think I’ll use it in the near future for our subscriptions too (assure that subscriptions go out after ETL has finished).

    Keep it up!
    Chris

  4. Lohic Beneyze

    Hi Ronald, thanks a lot for this clear blog with full tips. I’ve implemented it and it works lovelly.

    Maybe instead of using dbo.ExecutionLogStorage, the DMV ExecutionLog3 can be used. There is a column “Source” that indicates if the report has been rendered from Cache or Live or Session (Indicates a follow up request within an already established session)

    SELECT EL.ItemPath, EL.TimeStart, EL.TimeEnd,EL.*
    FROM dbo.ExecutionLog3 AS EL
    WHERE TimeStart > ‘2013-11-12’
    AND ItemPath like ‘%PS_ManagementRapportage Productiviteit%’

    I’ve just one issue and I’m wondering if you run into it.
    I’ve configured the caching for 2 reports: the reports are exactly the same but with 2 different names.
    When I execute the addevent for the shared Schedule ForCaching and check if the reports has ReportServerTempDB.dbo.ExecutionCache
    Only 1 report appear.

    Do you have any idea why the other report is not being cached?

    I’m using SQL Server 2012 SP1. The reports are deployed on a Sharepoint 2013 site.

    Thanks in advance.

    • Ronald Kraijesteijn

      Hi Lohic,

      It’s been a while since I was programming this piece. I have not run into this issue. Every report has an unique ID so even when they are the same, they are unique in the database.

  5. Awesome article, just what I was looking for. Thank you.

    • Ronald Kraijesteijn

      You’re welcome. It’s really cool to manipulate thing in the SSRS background. You can build your own subscription system in a front/backoffice application and “push” thing to the SSRS database, really cool.

  6. Glad I found this post, thanks for taking the time to clearly walk through it! We have a very similar setup, where we “expire” the shared schedules as a last step in our ETL processing. And, similar to what you’ve experienced, we occasionally see cases where the cache is not actually cleared out, even though the schedule was expired. We added a delay step, but this didn’t always resolve the issue. After further investigation, we’re seeing deadlocks on the report server after we call the “AddEvent” sproc.

    The deadlock info shows that the delete statement in “FlushReportFromCache” is always the victim; this sproc gets called somewhere after a shared schedule event is added to the events table. The circular lock involves the “IX_SnapshotDataID” index in the dbo.ExecutionCache table and the “IX_SnapshotData” index in the dbo.SnapshotData table. The deadlocks result in old data remaining in TempDB.

    Have you experienced this at all? If so, do you have a solution to get around these deadlocks? Thanks!

    • Hi Chris,

      Thanks for the comment’s. I havent’faced this issue myself so no experience with fixing this thing. Did you try to delete the cache record “hard” with a delete statement?

      • Thanks for the quick response. We were trying to avoid a “hard” delete if possible, but I’ll keep that approach in my back pocket if I can’t figure anything else out. If I find a solution, I’ll post it in the comments here.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *