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
Best Answer
The documentation states that you should not enable snapshot isolation on SSRS database.
Check here:
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:
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.
(advice on how to set up a second reporting instance)