July 31, 2011 at 4:51 pm
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
August 1, 2011 at 5:17 am
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?
August 1, 2011 at 7:35 am
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
August 1, 2011 at 7:38 am
I've never seen anything else that could explain this... will hang around just in case.
August 1, 2011 at 7:52 am
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
August 1, 2011 at 7:54 am
Are you using nolock hints or read uncommitted?
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
August 1, 2011 at 7:58 am
August 1, 2011 at 7:58 am
Ninja's_RGR'us (8/1/2011)
I've never seen anything else that could explain this... will hang around just in case.
:ermm:
August 1, 2011 at 8:02 am
August 1, 2011 at 8:04 am
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
August 1, 2011 at 8:06 am
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
August 1, 2011 at 8:11 am
August 1, 2011 at 8:13 am
BeerBeerBeer (8/1/2011)
I wish, but all we have isA 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?
August 1, 2011 at 1:12 pm
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
August 8, 2011 at 6:43 pm
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply