SQL Monitor 2 and Deadlocks ... ?

peter ehpeter eh Posts: 17 Bronze 2
edited December 1, 2010 9:42AM in SQL Monitor Previous Versions
I'm seeing some very strange behaviour here ...

After installing, I tested SQL Monitor 2 in our development and test environment. Everything looks fine there. Then I monitored two production servers. After e short time (maybe one hour) both servers began to show deadlocks. Both servers are Win2003, SQL2005 SE with latest patches on it. I've seen no deadlocks there for at least one year and now I'm getting 20 to 40 per day.
After taking away the monitoring from those servers and stopping the SQL Monitor services on the web server, the deadlocks still won't disappear.

According to the FAQ, SQL Monitor didn't change anything on the servers that it monitors. Is this true? Really no change in any sql-server settings?

Any other ideas for this strange behaviour?

Thanks for your help
Peter

Comments

  • Hi Peter

    SQL Monitor has "zero-impact" in that we don't install anything on monitored servers. Obviously we do cause some overhead as we're running queries against the servers. However, these queries avoid locking so shouldn't be causing any deadlocks.

    Would you be able to post any information about the deadlocked queries? We can check these just to be sure that SQL Monitor isn't causing any issues.

    Many Thanks
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • peter ehpeter eh Posts: 17 Bronze 2
    Hi Chris

    - I suppose that we have those deadlocks before I tried SQL Monitor, but we don't see them in the logs.
    - The deadlocks still appear now, even after the shutdown of SQL Monitor.

    -> something must have changed!

    After some research I think I found the reason. SQL Monitor must have enabled Trace Flag 1204, which isn't enabled on our production servers usually.

    Can you comfirm this Trace Flag activation?

    Thanks for your help
    Peter
  • Hi Peter,

    I've checked with the relevant developer and yes you're correct, we do enable Trace Flag 1204 in order to get information about deadlocks. We also have an alert that will inform users if we're unable to do this.

    Apologies for the misinformation. I've had a quick look at our online help information and can't find an FAQ. Would you be able to point me in the direction of the page that states that we don't change anything on the servers? I think we need to re-word it.

    Regards
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • Hi again

    Actually I've found http://www.red-gate.com/products/SQL_Monitor/faq.htm

    I presume this is the FAQ you mentioned?

    Thanks
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • peter ehpeter eh Posts: 17 Bronze 2
    Hi Chris

    Yes this is the FAQ I've read.

    Thanks
    Peter
  • Hi Peter

    I've raised an enhancement request (ref SRP-2714) to review the FAQ and make this more clear.

    Thanks
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
Sign In or Register to comment.