• jessh (8/25/2012)


    They can also be a basis for distributed negotiation, of course. Unfortunately, SQL Server seems ill suited to such usage because there's no mechanism for it to recognize dead connections in any timely manner.

    As I already explained, if the connection drops, any transaction that connection held will be rolled back, any locks it held will be released.

    It's easy to test, but if you prefer I do it, no problem. SQL 2008.

    I'm going to use SQLCMD as a test.

    One session open from SQLCMD with an open transaction

    Now I can't pull the network connection (both clients and DB server are the same machine), so I'll just close the window (X in the top-right)

    Session has gone from the SSMS query

    If I go and look in Testing DB, that transaction's gone and so's the X row lock that it held.

    If you want to try the same test with network connections, please do. I just don't feel like hauling my laptop out or booting VMs today.

    One major difference between Oracle and SQL server. Many of the things where in Oracle there's settings to change don't have comparable settings in SQL Server because it's taken care of automatically. Doesn't mean SQL doesn't have the feature, just that the settings aren't tweakable.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass