(5 + 1) Steps to Resolve Network Related Instance Error

A thought to blog inspired from spending ample of time resolving a very common error in SQL Server while connecting to a instance i.e. "A network-related or instance-specific error occurred while establishing connection to SQL Server. The server was not found or was not accessible …". Below are the steps to follow to resolve the error.

1.    Check Services: "SQL Server" and "SQL Server Browser" is up and running. We can check this by going to "All Programs à SQL Server à Configuration Tools à SQL Server Configuration Manager" OR search "SQL Server Con" in windows search.

2.    Check Client Protocols: Now check for the native client protocols e.g. TCP, Named Pipe etc. is enabled. Most of the times we'll get this information in error message towards end of it. Enable the provider if it is not done. Check image below.

3.    Check Server Protocols: Check the "SQL Server Network Configuration" and make sure that respective client protocol is enabled here as well. Check the image below.

4.    Know ports and open them: Now check the properties of the protocol by right clicking and selecting properties or double clicking on it. Select "IP Address” tab and go till end of the window till "IP All" comes as in image below. Check for the port where SQL server is listening in this case it is 3680 (not 1433 which is default) and it is dynamic. We can specify static as well in "TCP Port" section. Now we need to open the same port in this case it is 3680 in the local machine by creating inbound rule (or exception) in "Windows Firewall". We may also need to open the port for "SQL Server Browser Service" as well.

5.    Check Remote Connection: Make sure that SQL Server is enabled for remote connection by running below T-SQL command or from the below screen by “Right Click on the Server from SSMS à Select Connections”.

SP_CONFIGURE 'remote access', 1 RECONFIGURE WITH OVERRIDE GO

6.    Force Protocol and Port: While connecting from SSMS we can also provide port and protocol to connect.

Protocol can also be forced by prefixing protocol name e.g. "tcp:ServerName, PortNumber" TCP/IP or "ip:ServerName, PortNumber" for named pipe etc. Same can be achieved from "Options >>" option as well.

SQL Server Database Showing in Recovery

 It was 7 O’Clock in the evening and I was about to step out of the office but found one of the colleagues looking disturbed and struggling and on asking came to know that the database of the production application is showing in recovery and recovering since almost 4-5 hours. This happened after some new code deployment in the production server. The best part was that they were having backups till the time of failure and application can recover just by restoring the backups. So we thought of a quick action plan and executed as below.

·         Check what is blocking the database recovery which can be done by inspecting below query.

SELECT DB_NAME(dbid),* FROM dbo.sysprocesses WHERE spid > 50 and spid <> @@SPID

·         Try to kill the blocking SPIDs but got the error message “Only user processes can be killed”.

·         Try to delete the database by putting it to single user mode but the execution was just going on and on inspecting the query in first step found that drop database was waiting on recovery to finish.

·         Try to make the database offline but it was also waiting on recovery to finish.

·         Lastly we decided to

a.    Stop the server because anyway the application was already down.

b.    Then we cut and pasted the ldf, mdf and ndf files to another folder (Actually cut-paste is very fast option as it just changes the pointer in the disk).

c.    Start the SQL Server service again the recovery mode was gone.

·         Delete the database and restored the latest backups.

The query to find the file path of a database is as below.

SELECT D.dbid, D.name, F.physical_name FROM sys.master_files F JOIN dbo.sysdatabases D on F.database_id = D.dbid

This may not be a perfect way to deal with the situation but it worked  very quick and nicely because recovery is done through files and when there are no files no recovery can be done. Moreover we were having liberty to restart the SQL Server service as well which others may not have on these scenarios.