ERROR : (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error)

Few days ago, I had redone my local home network. I have LAN setup with wireless router connected with my four computers, two mobile devices, one printer and one VOIP solution. I had also formatted my primary computer and clean installed SQL Server 2008 into it. Yesterday, incidentally, I was sitting in my yard trying to connect SQL Server located in home office and suddenly I stumbled upon the following error. Finding the solution was the most infuriating part as it consumed my precious 10 minutes.

Let us look at few of the common errors received:

An error has occurred while establishing a connection to the server.

(provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

I eventually found the resolution to this error on this blog itself; so I was saved from hunting for the solution. I am sure there are a number of developers who had previously fixed this error while installing SQL Server 2008 or SQL Server 2005 but in due course forgot the right solution to this error. This is just a quick refresher. Make sure to bookmark this as you never know when you would need this solution.

Let us check into the steps to resolve this error.

1) SQL Server should be up and running.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Services, and check if SQL Server service status is “Running”.

In addition, ensure that your remote server is in the same network. Run “sqlcmd -L” in your command prompt to ascertain if your server is included in your network list. You can even find tutorial for the same here SQL SERVER – Find All Servers From Local Network – Using sqlcmd – Detect Installed SQL Server on Network.

I have confronted numerous situations when these nerve-wracking errors crop up, and most of the time when I try to troubleshoot I notice that SQL Server services are neither running nor installed. If SQL Server is not installed as default instance SQL Server Browser should be running together with it; we will explore this further in Topic 5.

 

2) Enable TCP/IP in SQL Server Configuration

When two or more SQL Servers are connected across network they do all communication using TCP/IP. The default port of SQL Server installation is 1433. This port can be changed through SQL Server Configuration Manager. TCP/IP should be enabled for SQL Server to be connected.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> Select TCP/IP

Right Click on TCP/IP >> Click on Enable

You must restart SQL Server Services for all the changes to take effect. Right click and go to menu properties to select location where default port of SQL Server can be changed.

3) Open Port in Windows Firewall

Windows Firewall is very efficacious in protecting the Operating system from all sorts of malicious attacks. By default, many of the ports and services are refrained from running by firewall. Time and again, SQL Server ports are not open in firewall as well. All the ports on which SQL Server is running should be added to exception and firewall should filter all the traffic from those ports. As described, by default SQL Server runs on port 1433, but if default port is changed then the new port should be added to exception. If SQL Server has named instance (another instance besides default instance) is installed, SQL Server browser should also be added to the exception, as described in Step 7.

Go to Control Panel >> Windows Firewall >> Change Settings >> Exceptions  >> Add Port

Make the following entries in popup “Add a Port” and click OK.
Name : SQL
Port Number: 1433
Protocol: Select TCP

4) Enable Remote Connection

Enabling remote connection is another important, yet oft-neglected step that is frequently missed by database administrators while setting up SQL Server. If this feature is turned off SQL Server will function smoothly on local machine, but it will let another server connect to it remotely. By default this feature is ON in SQL Server 2008.

Right click on the server node and select Properties.

Go to Left Tab of Connections and check “Allow remote connections to this server”

5) Enable SQL Server Browser Service

If SQL Server is not installed as default instance but instead installed as named instance and also if there is no specific TCP/IP port configured, it will give rise to the error that is being discussed in this article. If SQL Server Browser service is enabled, it will allow the server to be connected through dynamic TCP/IP port. Enabling this service is a one-time process, as on enabling it once it will apply to all the instances installed on the same server.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Browser

 

Right Click on SQL Server Browser >> Click on Enable

6) Create exception of sqlbrowser.exe in Firewall

As elucidated in Step 6, sqlbrowser service needs to be enabled for namedinstance. Windows Firewall may prevent sqlbrowser.exe to execute. So, it is imperative to add exception for the same in windows firewall.

Search for sqlbrowser.exe on your local drive where SQL Server is installed. Copy the path of the sqlbrowser.exe like C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe and create the exception of the file in Firewall, as delineated in Step 3.

7) Recreate Alias

It is getting quite common to create alias of SQL Server and use it in application. This will ensure that in future if any physical SQL Server has to be moved, it will not be required to change any code or connection string. You can simply createalias with the same name pointing to different SQL Server and it will start working instantaneously. I have observed that a couple of times due to internal error while recreating alias this error was fixed.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Native Client 10.0 Configuration >> Aliases

Delete the alias that is giving problem and recreate it with identical parameters.

 

I have tried my best to include all the methods of fixing this error and if I have missed any, please leave a comment and I will be very glad to include them here. I have put in my effort to encompass this issue in one article that needs to be refereed when any connection error comes up.

 

 

SQL Network Interfaces,error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

I have installed SQL Server and Management Studio on new machine and facing issues while connecting to SQL Server through remote machines on my local area network. After thorough searching and experimenting I have got resolved the error. Following are the steps(not necessary all) to resolve the issue. 

SQL Server should be running

Go to All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager. Select node SQL Server 2005 Services, under SQL Server Configuration Manager parent node. SQL Server's state should be Running. SQL Server Browser service should also be Running(you can Start/Stop by right click on service name) 

Enable TCP/IP in SQL Server Configuration

Go to All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager. Select node Protocols for SQLServer(your server's instance name) , under SQL Server 2005 Network Configuration. 
TCP/IP's state should be Enabled. (can be Enabled/Disabled by right click on Protocol Name) 

App Exception for SQL Server TCP Port in Windows Firewall

Go to Conctrol Panel > Windows Firewall > Exceptions (tab). Click button Add Port, enter name and port number 1433. Make sure your entered name is displaying in Programs and Services section, and should be checked while pressing OK button on the tab. 

App Exception for sqlbrowser.exe in Windows Firewall

In Firewall window > Exceptions tab, click on Add Program. Locate the sqlbrowser.exe by Browse button. Click OK. At my system, it is located at C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe. 

App Exception for sqlservr.exe in Windows Firewall

Similarly add sqlservr.exe in Firewall Exceptions. At my system, it is located at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe. 

Enable Remote Connection

Right click on SQL Server main node in Management Studio, and go to Properties. SelectConnections page from Select a Page section. Allow remote connections to this server should be checked. 

References

·                     http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

·                     http://msdn.microsoft.com/en-us/library/cc646023.aspx

SP_WHO2 information

One of the first lines of defense in determining the causes of database slowdowns is to use sp_who2. sp_who2 shows all the sessions that are currently established in the database. These are denoted as Spid‘s, or Server process Id’s. Running sp_who2 is easy, all that is required is to type sp_who2 and execute it, however it’s a good idea to call it with EXEC.

EXEC sp_who2

Results of sp_who2

The first 50 results are system spids. Generally these do not effect slowdowns of the system. These system events include the Checkpoint writer, Log writers and task schedulers. User processes are SPID numbers 50 and over. In diagnosing slowdowns it is these SPIDs that are the potential resource hogs.

sp_who2 also takes a optional parameter of a SPID. If a spid is passed, then the results of sp_who2 only show the row or rows of the executing SPID.

EXEC sp_who2 243

There are four main things to look for when when diagnosing slowdowns.

1.       Blocking

2.       High CPU usage

3.       High IO usage

4.       Multiple entries for the same SPID representing parallelism.

When a number is shown in the column named BlkBy, this represents the SPID that is currently stopping the SPID in the row shown. Sometimes many rows will show SPID numbers in the BlkBy column. This is because there is a chain of blockers. The way this occurs usually starts with one "lead" blocker blocking another process. In turn, the process that is being blocked, blocks others. This occurs down a chain. It can be a messy situation. In order to rectify, you may have to kill the lead blocker. If it happens often, you will want to research why this particular process is blocking. So, before you kill any process, find out what statement it is running first. To do this, execute DBCC INPUTBUFFER(SPID).

High CPUTime or High DiskIO time is usually spotted by comparing the relative CPUTime to the DiskIO time. It should be noted that CPUTime and DiskIO time represent the sum of all executions since the SPID has been active. It may take some training before you are able to spot a high number here. At times, you will see very high CPUTimes and almost no corresponding DiskIO. This is usually indicative of a bad execution plan.

Multiple rows that have the same SPID are known as Parellel processes. This happens when SQL Server has determined that a particular query is going to take a long time to run (according to the parallel settings at the server level). When this happens, SQL Server will launch multiple threads for the same procedure. When this happens often, it can have a devastating effect on SQL Server. Particularly IO. To rectify, either raise the query threshold for parallelism or turn down the maximum processors that parallelism can use (Max DOP) Max degree of parallelism, a setting at the server level.

Temp DB size and Growth parameters

You can view the size and file growth parameters of the tempdb data or log files by using following script :

  

 

SELECT

    name AS FileName,

    size*1.0/128 AS FileSizeinMB,

    CASE max_size

        WHEN 0 THEN 'Autogrowth is off.'

        WHEN -1 THEN 'Autogrowth is on.'

        ELSE 'Log file will grow to a maximum size of 2 TB.'

    END,

    growth AS 'GrowthValue',

    'GrowthIncrement' =

        CASE

            WHEN growth = 0 THEN 'Size is fixed and will not grow.'

            WHEN growth > 0 AND is_percent_growth = 0

                THEN 'Growth value is in 8-KB pages.'

            ELSE 'Growth value is a percentage.'

        END

FROM tempdb.sys.database_files;

GO

I hope this will helps you all

 

Tempd DB resize (modify size) using script

use master
 go

 alter database tempdb modify file
  (name = tempdev,
  size = 8192MB,
  maxsize = 10241MB,
  filegrowth = 2048MB)
 go

 

 alter database tempdb modify file
  (name = templog,
  size = 512MB,
  maxsize = 1025MB,
  filegrowth = 256MB)
 go
 checkpoint
 go

Checkpoint operations

 

Factors Affecting the Duration of Checkpoint Operations

 

In general, the amount time required for a checkpoint operation increases with the number of dirty pages that the operation must write. By default, to minimize the performance impact on other applications, SQL Server adjusts the frequency of writes that a checkpoint operation performs. Decreasing the write frequency increases the time the checkpoint operation requires to complete. SQL Server uses this strategy for a manual checkpoint unless a checkpoint_duration value is specified in the CHECKPOINT command.

The performance impact of using checkpoint_duration depends on the number of dirty pages, the activity on the system, and the actual duration specified. For example, if the checkpoint would normally complete in 120 seconds, specifying a checkpoint_duration of 45 seconds causes SQL Server to devote more resources to the checkpoint than would be assigned by default. In contrast, specifying a checkpoint_duration of 180 seconds would cause SQL Server to assign fewer resources than would be assigned by default. In general, a short checkpoint_duration will increase the resources devoted to the checkpoint, while a long checkpoint_duration will reduce the resources devoted to the checkpoint. SQL Server always completes a checkpoint if possible, and the CHECKPOINT statement returns immediately when a checkpoint completes. Therefore, in some cases, a checkpoint may complete sooner than the specified duration or may run longer than the specified duration.

 

Checkpoint

For performance reasons, the Database Engine performs modifications to database pages in memory—in the buffer cache—and does not write these pages to disk after every change. Rather, the Database Engine periodically issues a checkpoint on each database. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log.

The Database Engine supports several types of checkpoints: automatic, indirect, manual, and internal. The following table summarizes the types of checkpoints


Automatic: EXEC sp_configure 'recovery interval','seconds'
Description: Issued automatically in the background to meet the upper time limit suggested by the recovery interval server configuration option. Automatic checkpoints run to completion. Automatic checkpoints are throttled based on the number of outstanding writes and whether the Database Engine detects an increase in write latency above 20 milliseconds.


Indirect: ALTER DATABASE … SET TARGET_RECOVERY_TIME =target_recovery_time{ SECONDS | MINUTES }
Description: Issued in the background to meet a user-specified target recovery time for a given database. The default target recovery time is 0, which causes automatic checkpoint heuristics to be used on the database. If you have used ALTER DATABASE to set TARGET_RECOVERY_TIME to >0, this value is used, rather than the recovery interval specified for the server instance.


Manual: CHECKPOINT [ checkpoint_duration ]
Description: Issued when you execute a Transact-SQL CHECKPOINT command. The manual checkpoint occurs in the current database for your connection. By default, manual checkpoints run to completion. Throttling works the same way as for automatic checkpoints. Optionally, the checkpoint_durationparameter specifies a requested amount of time, in seconds, for the checkpoint to complete.


Internal: None
Description: Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.

Sql server database states

 In sql server database having several states. The following are the database states.

ONLINE State :

          Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed.


OFFLINE State :

        Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.

 
RESTORING State :

          One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable.

 
RECOVERING State :

          Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.

 
RECOVERY PENDING State :

            Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.

 
SUSPECT State :

           SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed

 
EMERGENCY State :

        At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem.

 

 


I hope this article is useful to all users.