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
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.
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.
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.
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.