Siusic Dot Com

I have been dealing with SQL server many years and experienced numerous connection problems. So I am going to detail the ways that I used to trouble shoot and fix connection problems. If you are SQL server guru you may skip or post your input.

There are only a few root causes for connection problems:

1. SQL server is not running or malfunctioning
2. The network path between the client computer and the server is broken or blocked somehow
3. The account used to connect to SQL server doesn’t have enough access rights

It is pretty easy to identify the first situation. Open up SQL query analyzer on the server computer

locally and connect to SQL server. If it is able to connect then it is not a problem on the server. If connection times out then SQL Server is having problem or too busy to handle your request. To determine whether it is too busy you can simply open up task manager and look at the CPU usage. If you see CPU usage is consistently higher than usual then very likely SQL server is getting hit by some poorly written query or indexes were not properly built, or maybe a properly built index got dropped. If the server is not busy then most likely SQL server didn’t start up fully and it is likely that SQL is running on a service account that is not in the administrator group of the server computer.

For the second case you can open up a DOS window on your client computer. Run a telnet command to see if you can telnet to the TCP port that SQL server is listening on. For example:

C:\> telnet [severname] 1433
The standard TCP port that SQL server is listening on is 1433. But it can be changed using server network utility on SQL server 2000 or configuration manager on SQL server 2005. So before you do that you need to find out what is the port number. If you can connect to the port successfully you will see the window is cleared and the cursor is waiting for your input. If connection fails then you will see error message. There are many situations cause network connection failure. The most common one is DNS problem — The server name is not correctly resolved to the server’s IP address. To confirm that you may login to the server computer, open up a DOS window and run the “ipconfig” command. That command display the IP address of the computer. After that open up a DOS window on the client computer and run a “ping [servername]” or “tracert [servername]” command to see whether the IP address displayed in the ping result or the trace result is same IP address. If it is not the same then there may be DNS problem and you may need talk to the person in charge of the network. There might be firewall sitting in between the client and the server that blocks the communications. That also needs the cooperation of your network staff to fix it. The third case is easy. You just need to change the access right of the login name.

In the second case, sometimes you know it is index problem causing the database too busy since the database is online and a lot of users using your application which is sending the server a lot of queries and its performance depend a lot on the missing index. You want to build the index to fix it but you can’t because the server is too busy to handle your request and your connection request times out. Even you can connect to the server your query to build the index is likely to be blocked by other process. In that case you may think of unplugging the network cable so that users are not able to connect to the server but you. However the server is located in the data center 50 miles away. How to handle that? A better way is to pause SQL server so that SQL server can finish its work on hand while not taking any new requests. You should know that when SQL server in pausing mode, the existing connections and processes will be able to continue but SQL server will not take any new connections. After you pausing SQL for a while, your server CPU usage will subside. You can un-pause it and at the same time connect to the server right away. After you connect to the server, put the server in pausing mode again. Now you can run your query to build the index and the server is able to finish it because the server now can concentrate on your job and not taking any other job. After the index is built you can put the server in normal running stay.

Related Posts:

How to Kill AutoShrink Process

Whenever There Are School Shooting I Feel Shame on American and Proud of Chinese

Look Here if None of The Permalink Solutions is Working For You

SQL Server Backup Device Error or Device off-line

Error Reading Packet From Server Error Reading Log Entry Server_Errno=1236