1

I've got a SSRS report which is used for a wallboard to display basic stats and information to the department via a Raspberry Pi attached to a creen

This has the AutoRefresh Option set to 60s however since patching today where we turned on Snapshot Isolation to remove a lot of blocking that was occuring every morning and mid afternoon when the daily reports ran the auto refresh option seems to be failing.

I've tried re-deploying the report and even created a completely new report that just puts up the date& time to keep track of it refreshing, it sometimes works for a few minutes but then just stops refreshing

The tests I ran included a refresh every 10 seconds to see if it was timing out the execution running over 60 seconds

Out of the 5 separate executions I have just ran, one ran for 542 seconds before no longer refreshing, one ran for 339 one for 164 and two of them failed to refresh at all

Previous this would run until the wifi connection was lost on the Pi and the page errors while refreshing (NOTE all testing has been done locally on wired connection no network drops)

previous tests before that set have been done across different browses in / out incognito mode and all produce the same results

Any idea why this is happening, and potential fix that doesn't involve us stopping everything on the server again to turn off Snapshot issolation

1

The documentation states that you should not enable snapshot isolation on SSRS database.

Check here:

  1. Enabling Snapshot Isolation on the Report Server Database.
    You should follow the instructions there to turn it off.

    Excerpt from the docs:
    "...You cannot enable snapshot isolation on the report server database. If snapshot isolation is turned on, you will encounter the following error: "The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available."

As for the cause of the blocking, and ways to fix them, here are some links to help you:

  1. SSRS Blocking Processes
    SSRS issues locks to provide a multi-threading mechanism. Once all threads running the report that you wrote, end, the lock is removed. So if you see these blocks regularly, it is usually due to a slow query.
  2. Advice on setting up a Reporting server
    (advice on how to set up a second reporting instance)
  • The locks we're trying to get around are internal to SSRS for getting writelock and other things which block eachother, the only advice i could find around that was to enable snapshot issolation – Ste Bov Jul 26 '18 at 12:43
  • Excerpt from the docs: You cannot enable snapshot isolation on the report server database. If snapshot isolation is turned on, you will encounter the following error: "The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available." – Milan Jul 26 '18 at 12:50
  • Well thankfully the only error we've seen is that the autorefresh appears to stop working intermittently, will need to get it passed to get it reverted which is annoying, didn't see that documentation before – Ste Bov Jul 26 '18 at 12:53
  • @Ste Bov - I updated my answer, hope it helps – Milan Jul 26 '18 at 16:39
  • Yea, thanks, we managed to get a maint window in for thismorning to roll this change back. and haven't had the issue since, will look into those other methods of reducing the SSRS locks – Ste Bov Jul 27 '18 at 8:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.