SQL Connection getting dropped

  • I have a situation with one of our clients where one of our utility applications is losing it's SQL connection after it has successfully retrieved data.

    For instance, the customer can log into the application, load a list of members, but on trying to view an individual members details we'll get the following exception

    A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

    The Connection String uses an IP address to get to the server - don't know if this makes behaviour any different - I would doubt it though.

    Have tried it with pooling=false but no difference.

    Have elongated the server time out setting to no avail.

    We have multiple clients connection to the DB server in this fashion but only some of them are experiencing this behaviour.

    At this point, at a loss as to where to look next - hopefully someone has some fresh ideas about what might be happening - or can ask some questions that might open up those ideas

    TIA

    Steve


    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • Are those users connected on a db with log-shipping?

    Do you kill connection to resolve blocking?

    Do you reboot the server (or service) on a regular basis?

  • Ninja's_RGR'us (8/1/2011)


    Are those users connected on a db with log-shipping?

    No

    Do you kill connection to resolve blocking?

    No

    Do you reboot the server (or service) on a regular basis?

    No

    If I can work my way down this list

    There is no log shipping - effectively there is no real DBA in control. You'd have to live outside a large country to understand the small systems we work with (actually, they're large to us ... ) 😉

    The connection is getting killed during processing of a query - no need for us to kill it. No, the application does not kill the connection (though I will review this)

    If you have to reboot your server, you're doing it wrong in the first place. But the direct answer is No.

    Appreciate your questions, hope my answers lead to more

    Regards

    Steve


    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • I've never seen anything else that could explain this... will hang around just in case.

  • I agree.

    Very weird

    The fact that the connection happens and data is retrieved, THEN the connection is lost ???

    We have other environments where this utility is running without any issue whatsoever.

    One last piece of information (which shouldn't make any difference) is that the DB is sitting behind a Terminal Server environment for the actual application. We are connecting direct to the SQL Server instance with our connection string for this utility app, ignoring the TS setup.

    Don't know if that makes a difference - again suspect not.

    Again, thanks for questions, suggestions, ideas.

    Steve


    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • Are you using nolock hints or read uncommitted?

    http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

  • There are no hints in the DB queries and we do not suggest how the lock should be - basically we trust SQL Server


    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • Ninja's_RGR'us (8/1/2011)


    I've never seen anything else that could explain this... will hang around just in case.

    :ermm:

  • I do believe that is a similar face to mine once I was told this was happening ...


    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • is there a query exception recorded in e.g. errorlog ( deadlock / error with Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • ALZDBA (8/1/2011)


    is there a query exception recorded in e.g. errorlog ( deadlock / error with Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.)

    Shortcut to read those files :

    CREATE TABLE #logs (LogDate DATETIME, ProcessInfo VARCHAR(50), Txt VARCHAR(MAX))

    INSERT INTO #logs (LogDate, ProcessInfo, Txt)

    EXEC master.sys.xp_readerrorlog 0,1

    INSERT INTO #logs (LogDate, ProcessInfo, Txt)

    EXEC master.sys.xp_readerrorlog 1,1

    INSERT INTO #logs (LogDate, ProcessInfo, Txt)

    EXEC master.sys.xp_readerrorlog 2,1

    SELECT * FROM #logs

    WHERE LogDate BETWEEN DATEADD(D, -1, DATEDIFF(D, 0, GETDATE())) AND GETDATE()

    -- AND Txt NOT LIKE 'The activated proc %'

    -- AND Txt NOT LIKE 'Login failed for user%'

    -- AND Txt NOT LIKE 'Erreur : 18456, Gravité : 14%'

    AND Txt NOT LIKE 'Log was backed up. Database: PROD-FORDIA%'

    -- AND Txt NOT LIKE '%Trace%'

    AND Txt NOT LIKE '%TEST_RESTORE%'

    AND Txt NOT LIKE '%DemoRestoreTable%'

    AND Txt NOT LIKE '%DemoRecover%'

    AND Txt NOT LIKE '%CHECKDB%'

    -- AND Txt NOT LIKE '%PROD-BACKUP%'

    AND Txt NOT LIKE '%PROD-FORDIA_test_restore%'

    -- AND Txt NOT LIKE '%Starting Job Ceridian Paye XLS%'

    -- AND Txt NOT LIKE '%PROD-FORDIA_test_moul_FF80%'

    AND Txt NOT LIKE '%cachestore flush%'

    AND Txt NOT LIKE 'Database backed up. Database%'

    AND Txt NOT LIKE 'This instance of SQL Server has been using a process ID%'

    AND Txt NOT LIKE 'SQL Trace %'

    AND Txt NOT LIKE 'Erreur : 18456, Gravité : 14, État : 8.'

    AND Txt NOT LIKE 'I/O was resumed on database%'

    AND Txt NOT LIKE 'I/O is frozen on database%'

    --WHERE LOWER(Txt) LIKE '%memory%'

    ORDER BY LogDate DESC

  • I wish, but all we have is

    A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)


    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • BeerBeerBeer (8/1/2011)


    I wish, but all we have is

    A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

    Can you upload the log files, I'll take a look at them?

  • If the errorlog doesn't bring any refs to your issue, I think best is to start a trace which also captures oledb errors and run your app until the issue occurs.

    double check to include the actual sql statement so you can have a clue what is actually causing the break off.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • OK, running that script didn't reveal anything so I'll have a look at creating a trace that might trap the errors.


    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply