question

peter.smout avatar image
peter.smout asked

Server 2008 Cannot connect to named instance

Running Windows Server 2007 SP1 64 bit on a VM (VMWARE) .. Connecting to a named instance either remotely or locally has gotton progressively more difficult until now it cannot be connected to at all always giving Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (.Net SqlClient Data Provider) This instance hosts a number of mirrors which still work and will reconnect after a restart or server reboot SQLCMD is the same will not connect , the only thing that seems different now is that the instance is using all available memory at least 20gb which drops back after a restart In summary cannot connect to named instance SQL2-1(slash)CISMSSSERVER (in this case) via SSMS or SQLCMD has gotton progressively worse over the last 6 weeks not sudden change , the default instance thats runs on the same server is fine . Points to a resource problem but a reboot should resolve that ,which it doesnt. o) Not Firewall related (switched off) o) Various timeout values have been tried o) Connecting using SSMS or SQLCMD gives same result o) using port 63253 o) Number of databases on instance 275 Anyone got any ideas ? I am flummoxed
timeout-expired
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
Is the SQL Server Browser service running? You need that to enable connections to a named instance.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

peter.smout avatar image peter.smout commented ·
Hi yes Browser service is running Thanks
0 Likes 0 ·
sqlaj 1 avatar image
sqlaj 1 answered
Have you tried using the IP Address? Changing the port you are using to something different? Is there another app on the server using that port? Resource Related: Check the memory settings for each instance. Are they the default? If so you need to look at setting the Max Memory to an appropriate level based on the instance usage/workload and total on the server.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

peter.smout avatar image peter.smout commented ·
I cant access the instance at all but I do know the max memory was set to 5gb currently its using around 22gb thanks
0 Likes 0 ·
SQLDBA123 avatar image
SQLDBA123 answered
Try below - Create alias name(64 bit and 32 bit )for named instance and try connect. - Try other option like Test.udl file creation as given below 1. Open notepad on the server or outside server and save as Test.udl.Then close it. 2. Now Open Test.udl (Data Link Properties) Give server name ( IP_address,port_number) this way you can test the connectivity using udl ext file.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

peter.smout avatar image peter.smout commented ·
It wont allow me to connect via alias either same timeout period elapsed error Connecting using udl fails as well thanks
0 Likes 0 ·
JohnM avatar image
JohnM answered
Have you tried to get in through the DAC? It would have it's own thread for this specific purpose. You would have to RDP into the server and do it from there. You can utilize the DAC from a remote connection. That should give you the ability to run queries to see what might be consuming the resource that are preventing you from login into the instance. Just a thought.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

peter.smout avatar image peter.smout commented ·
DAC is not enabled so scuppered with that one thanks
0 Likes 0 ·
JohnM avatar image JohnM commented ·
The DAC, as far as I know, by default is enabled for local connections only and I'm not aware of a way to "turn off" the DAC. You can enable/disable "remote" connections but not local. Just curious, What makes you think that it's disabled? I could be wrong but I've never seen documentation on how to turn both local & remote connections off. http://msdn.microsoft.com/en-us/library/ms190468(v=sql.100).aspx Hope that helps!
0 Likes 0 ·
peter.smout avatar image peter.smout commented ·
Ok Cannot access DAC through SSMS but can through sqlcmd sporadically thanks
0 Likes 0 ·
VishalhSingh avatar image
VishalhSingh answered
Based on what you have explained it looks like the SQL Server DBE is drained 9out of memory). For some or the other reasons (generally happens when MIN/MAX memory is either not allocated or allocated too less to handle the workload) when a new connection request is coming to SQL Server, it needs some bit of memory resources for background purposes (like authentication, authorization etc). Since it is already running out of memory it can'e arrange any more memory to fulfill this new connection request. What you can do (not in any specific order): 1. Check the total physical memory available on the server and how much ws allocated to SQL Server (increase if there is any scope of increasing but this increment is only temporary). You can use sp_configure do increase the memory immediately after restart. Since you are restarting the SQL Server, try executing sp_cofigure 2. Restart the SQl Server in minimal configuration load and then connect to check which DB/processes ar causing the memory pressure and disable that for a while so that you can come back and optimize that. Let me know if the above helps. In case they aren't we will go much deeper and fix this.
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

peter.smout avatar image peter.smout commented ·
Server has 32gb of memory default instance is using 2gb named instance shows its using 3gb in task manager but is actually using 25 gb which can be seen by the amount of memory returned when the instance is restarted I cant connect to the instance to run any queries or look at any configuration .. this has gradually deteriorated over the last few weeks to the extent that i cannot access the instance at all now thanks
0 Likes 0 ·
VishalhSingh avatar image VishalhSingh commented ·
is the restarting with minimal configuration option also not helping?
0 Likes 0 ·
peter.smout avatar image peter.smout commented ·
Unable to start in single user at the moment as this is a live server
0 Likes 0 ·
VishalhSingh avatar image VishalhSingh commented ·
Understood. But how is your other processing doing on that server are then getting through or they are stuck-up? If yes, your server is anyhow in a deadlock but if not. Try restarting it in a off-peak hours. I am sure that this will work.
0 Likes 0 ·
peter.smout avatar image peter.smout commented ·
Hi there This server has been restarted a number of times , this used to help some weeks ago but not any more ,also the instance has been restarted also and although this does free up memory it doesnt allow connection thanks
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.