Filtering blocking alerts from SSRS Report Catalog databases
Luke L
Posts: 43 Bronze 3
Hello, I'm having some difficulties filtering some blocking processes from SSRS report catalog databases.
[dbo].[CheckSessionLock] is the blocked process, but the blocking information shows as unknown in the SQL monitor alert. I believe the blocking process is actually running dbo.WriteLockSession.
Since SQL monitor doesn't see the process will it know to filter these alerts if I put WriteLockSession in either of the exclusion text boxes?
We know these are long running reports and have no ability to rewrite them at this time and we're trying to remove some of the noise.
Thanks,
-Luke.
[dbo].[CheckSessionLock] is the blocked process, but the blocking information shows as unknown in the SQL monitor alert. I believe the blocking process is actually running dbo.WriteLockSession.
Since SQL monitor doesn't see the process will it know to filter these alerts if I put WriteLockSession in either of the exclusion text boxes?
We know these are long running reports and have no ability to rewrite them at this time and we're trying to remove some of the noise.
Thanks,
-Luke.
Tagged:
Answers
What version are you currently on? There have been several fixes to prevent the unknown from appearing so I want to confirm that. If that is the case then after updating it should display some further information to aid in the filtering.
Also, are you able to share a screenshot of the top section of the alert? If not here, I can reach out through a support ticket since you are supported.
Kind regards,
Alex
Have you visited our Help Center?
Thanks for taking a look.
-Luke.
That is going to be the issue I mentioned which has been fixed in the latest version.
When the database name is shown as Unknown it means that the sample was taken when the process was sleeping and so we weren't able to get the information.
Several improvements have been made to address this which should allow some information on the SQL of the Blocking process to be displayed and then you should be able to filter that using regular expressions in the boxes provided in the alert configuration for the Blocking process alert.
Do let me know if you do not get further information or you are not able to exclude them using the regular expressions once you have updated to the latest version (9.0.19 currently).
Kind regards,
Alex
Have you visited our Help Center?
I've added the following to both of the exclude boxes for that instance but still get the blocking alerts for very long running reports.
CheckSessionLockWriteLockSession
Below is a screenshot of the alert. As you can see the database Name is still unknown, however the blocking SQL fragment contains the writelocksession procedure, and the Blocked SQL Fragment contains CheckSessionLock, so I would think the above would work.
Please let me know if you have any ideas.
Thanks,
-Luke.
As you're supported I'm going to reach out to you via a support ticket to get the log files which should include the alert settings to see if I can see anything that might be preventing this from working. You should get that shortly!
Kind regards,
Alex
Have you visited our Help Center?
We then search for the last available query text we have associated with the process and display that in case it helps, but we are not able to filter on this.
We are going to add an informational message indicating that this is the case to avoid future confusion.
Kind regards,
Alex
Have you visited our Help Center?
Some good news!
It turns out the last available query text fragment is available in the alerting code (which does the filtering) so they think they can filter on this after all. They think they might be able to get this in for version 9.2.2 which I believe is coming out next week, so please keep an eye out for that and let me know!
Kind regards,
Alex
Have you visited our Help Center?