AnsweredAssumed Answered

Monitoring SQL deadlock

Question asked by AlexCote Champion on May 11, 2017
Latest reply on Jun 16, 2017 by AlexCote

Hello PISquare,

 

I have multiple real-time applications (mainly C# Windows Services) built over AF and last Sunday, they all went down because of the "OSIsoft AF Maintenance" SQL job on the PIFD database that reached a deadlock state. I will take care of this one but this remind me that we aint fully protected against this kind of global failure and what I would like to know is:

 

  • Is there a way to monitor deadlocks on PIFD?
  • Is there a AFSDK class that monitor AF connection to SQL database?
    • If not, have you ever implemented some kind of watchdog that would continuously check the connection state to the SQL database.
  • Plan C, I thought of using the System.TimeoutException I received when playing with EFs (SetEndtime(), CheckIn(), etc.) but I don't like this as it require to reach a long timeout and I need quick response time.
    • Ex: System.TimeoutException: This request operation sent to net.tcp://piafserver.{domain}:5457/AFServer/Service did not receive a reply within the configured timeout (00:05:00). The time allotted to this operation may have been a portion of a longer timeout.  This may be because the service is still processing the operation or because the service was unable to send a reply message.  Please consider increasing the operation timeout (by casting the channel/proxy to IContextChannel and setting the OperationTimeout property) and ensure that the service is able to connect to the client.

 

My objective is have my apps know when they loose comm with the SQL server / database and act accordingly (Generate alarm, disable processing, send email, etc)

 

Thanks for your help

 

Alex

Outcomes