SQL DM Alert

I am getting the alerts from SQL Diagnostic manager, session id blocking for 300 seconds.It doesn't look like that particular session id is active and it looks to me false alert. The blocking time is not increasing and the blocking happened once. But not sure why it keep sending the same alert even after there is no blocking.I am using 10.1 version. Has anyone came across this issues? How to fix the false alert. Please advise?

  • In SQLDM, Activity Monitor is the feature that captures blocking events. This feature gathers data either through Extended Events or a SQL Server Profiler trace (depending on how you configure it and the version of SQLDM you are on). 

    I've seen some scenarios where either the extended event file or the profiler trace file becomes corrupted. In such scenarios, SQLDM ends up reading the old data over and over and generating alerts based on the old data. What you can try doing is the following:

    1. In SQLDM, disable Activity Monitor (right-click on the monitored instance name, select Properties, go to Activity Monitor and disable it).
    2. Once disabled, right-click on the instance name and select Refresh Alerts (this will pass the change onto the monitored instance).
    3. Remote desktop onto the server hosting the monitored instance.
    4. Navigate to the logs directory (where the SQL Server error logs are located) for that particular SQL Server instance. For example, the directory for my 2017 instance is located at C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Log.
    5. Locate and delete the Activity Monitor related files.
      • If you used Extended Events, there will be some XEL files named similar to the following:

        InstanceNameAMExtendedEventLog_0_132269448534310000.xel - Notice the "AM" in the file name. It stands for activity monitor. Delete all of the XEL files with this naming scheme.
      • If you used SQL profiler traces, there will be some TRC files named similar to the following:

        ServerNamedm7ActivityMonitor.trc - Note the "dm7ActivityMonitor" part of the file name. Delete all files with this naming scheme.
    6. Once you've deleted the files, you can go back into SQLDM and re-enable Activity Monitor.
    7. Push the change to the monitored instance by refreshing alerts like in step 2.

    Give that a try and see if you continue to have the issue you reported.