.net - SqlServer wedged connection due to network drop -

we have 2 directly related problems can reduced simple piece of code. suppose machine 1 hosts application, , machine 2 hosts database, connected ethernet via hub. going simulate network problems unplugging network cables hub.

you "make network reliable". i. need prove it's not first explicit capture of problem before customers believe.

we cannot solve problem timeouts have /very/ long running queries , non-queries. yes, of them take hour or more, , users won't put staring @ frozen session long enough real error. kill instead.

using system.data; using system.data.sqlclient;  public class test {     public static void hang1()     {         using sqlconnection oconnection = applib.getconnection() // returns open connectin         {             using sqlcommand ocmd = new sqlcommand("waitfor delay 00:01:00", oconnection)                 ocmd.executenonquery(); // unplug cable between hub , database server when in call , call never returns         }      }       public static void hang2()      {             using sqlcommand otcmd = new sqlcommand("set transaction isolation level serializable", oconnection)                 ocmd.executenonquery();              using otransaction = new sqlclient.sqltransaction             {              using sqlcommand ocmd = new sqlcommand("select max(id) form table1")             {                ocmd.transaction = otransaction;                ocmd.executescaler();                system.threading.thread.sleep(60 * 1000);                // disconnect cable between hub , application server here                // table table1 locked , remain locked until transaction                // manually killed database server.                ocmd.executescaler();             }             }       }  } 

we seeking solution detect transaction stuck without having set timeout on it. if designing own tcp/ip protocol, i'd design heartbeat no response sufficient number of seconds = conclude dead, bail out, clean up. i'd way accomplish same idea, turn quiet hang noisy crash cleanup code can clean up.

this exists. called keep-alive.

see following information:

• http://blogs.msdn.com/b/sql_protocols/archive/2006/03/09/546852.aspx?redirected=true

• http://support.microsoft.com/kb/137983?sd=rmvp&fr=1

sql server probe connections keepalive packets , should detect within few minutes if client no longer present.

however doesn't - want client send keepalives make sure server still beavering away.

there seems no supported way of doing this. if wanted enable tcp keepalives on client socket have use reflection or unsafe code locate actual tcp socket , use wsaioctl enable keepalives. better solution given in other answer.