How do you use cloud databases? Take the survey.

Filtering blocking alerts from SSRS Report Catalog databases

Luke LLuke L Posts: 43 Bronze 2
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.
Tagged:

Answers

  • Alex BAlex B Posts: 1,037 Diamond 4
    Hi @Luke L,

    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Luke LLuke L Posts: 43 Bronze 2
    We're currently on 9.0.10.21294 but will be upgrading soon to the newest version to fix another issue.  He're a screenshot of the alert email.  not sure if you needed the info from inside of sql monitor itself and if so which tab.  If this isn't the correct info let me know and perhaps we can this to a support ticket.




    Thanks for taking a look.
    -Luke.
  • Alex BAlex B Posts: 1,037 Diamond 4
    Hi @Luke L,

    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Luke LLuke L Posts: 43 Bronze 2
    Alex, We've upgraded to version 9.1.3.23063.  I'm still unable to stop blocking alerts from some SSRS processes, and I'd appreciate some guidance.

    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.

    CheckSessionLock
    WriteLockSession

    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.




  • Alex BAlex B Posts: 1,037 Diamond 4
    Hi @Luke L,

    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Alex BAlex B Posts: 1,037 Diamond 4
    For anyone else coming across this, after working with @Luke L we've found that the issue is due to the query text not being available when the alert is being raised, so we are not able to exclude it based on the query text at that time.

    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Alex BAlex B Posts: 1,037 Diamond 4
    Hi @Luke L,

    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Luke LLuke L Posts: 43 Bronze 2
    That's great news.  I'm not sure how quickly we'll be able to get the next release installed since we just did an update, but I'll pass this along.  I'll let you know when we get it installed if we still see the issue.
Sign In or Register to comment.