July 2, 2015 at 2:28 am
I have been facing a ridiculous problem that I am able to connect SSMS but can't work properly in a particular server. It throws the following error message.
A transport-level error has occured when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) (Microsoft SQL Server, Error:121)
There are total 13 SQL Servers in my work station. I can access and work on rest 12 servers via SSMS. I can remotely access that server and get reply from ping.
I have restarted SQL Server & SQL Agent Services. Even I have restarted the server itself. But no result yet.
I am not getting any clue. Please help me to find out the solution.
Thank
July 2, 2015 at 3:39 am
Have a read of this, no point in me regurgitating it. Usually its down to memory pressure or IO:
MCITP SQL 2005, MCSA SQL 2012
July 4, 2015 at 10:12 pm
No solution at all. That article did not help me. Because I am not finding any RESOURCE_SEMAPHORE waittype. Please any more help.
July 6, 2015 at 5:49 am
Check sql server configuration manager.
TCP/IP.
named pipes enabled.
Try to connect using .udl file.
July 6, 2015 at 5:57 am
By this statement do you mean you are running 13 SQL server instances on your own PC? "There are total 13 SQL Servers in my work station."
If so what settings do you have for Min \ Max memory on each of your instances?
This will most likely be a memory issue, 13 instances for a workstation is a lot and it may be that between the other 12 they are using up all the memory of your workstation.
What happens with the problem instance if it was the only one with its services running on your workstation and the other 12 instances services stopped?
Although this error is also not limited to just SQL server, so it may on be reporting it up from SSMS as it also occurs in .net applications where network issues are encountered
MCITP SQL 2005, MCSA SQL 2012
July 7, 2015 at 10:12 pm
TCP/IP, named pipes, VIA enabled.
July 7, 2015 at 10:16 pm
No, Sir. I have meant that there are 13 different servers and 13 different instances. That means every server has only one single instance that is default "MSSQLSERVER". I can remotely access all of the 13 servers. I can also connect to 12 instances through SSMS. But in one server, it throws that error. Actually the word would be "office" not "work station". Sorry for the mistake.
I have not touched any memory configuration. All default values are set. I am not running any .net application just want to connect and work through SSMS.
July 8, 2015 at 2:50 am
Thanks for the clarification.
To try and narrow down the problem can you answer these questions:
1. Can you remote desktop onto the server that hosts the SQL server instance that you are having the issue with? If so does does it have SSMS installed and does it work when used on the remote desktop session?
2. Are you the only user that has the problem with connecting SSMS from your desktop machine to this particular instance
3. Has this always happened or is it just a new issue?
4. Have you tried connecting to the remote instance using the command line utility sqlcmd? Does this have the same issue when running queries?
5. Have you checked the TCP settings for the problem SQL instance as suggested by the other responses?
BTW, SSMS uses .net framework, so it itself is a .net application, so you are connecting with .net applications tot he problem SQL Instance.
MCITP SQL 2005, MCSA SQL 2012
July 8, 2015 at 10:52 pm
Welcome for your interest.
1. Yes, I can remote desktop onto the server and the server has SSMS installed and I can work on the remote desktop session.
2. Yes, I am the only user facing the problem with connecting SSMS from my desktop machine. There are two (2) more users but they are not facing the same problem.
3. It is a new issue. I have been working here for 3 years but no problem.
4. Yes, I have tried and I can successfully connect to the remote instance using the command line utility sqlcmd. No, I don't have the same issue when running queries.
5. Yes, I have checked the TCP settings as suggested by the other responses. All are enabled. No problem there.
July 9, 2015 at 3:23 am
Thanks for answering the questions. Given that this only affects yourself, other users are okay and there is no issue when you use SSMS within a RDP session on the server it suggests that its a network issue between your workstation and this instance.
I'm no network expert so can't really add much in the way of further advice, however after a bit of searching I found this msdn article which may help narrow down the issue:
https://support.microsoft.com/en-us/kb/325487
MCITP SQL 2005, MCSA SQL 2012
July 9, 2015 at 3:43 am
Thank you for your guidance. I will go through the link. Again thank you very much for being with me.
July 10, 2015 at 1:06 am
Look at log drive. Check the free space on it. Try investigate all around the log
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply