Sql server error: provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe

Question

I have written a windows form application with C#.Net using SMO for backup and restore the database. 
here is my connection string:

<connectionStrings>
    <add name="PhoneBookEntities" connectionString="metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\SQLEXPRESS;attachdbfilename=|DataDirectory|\PhoneBook.mdf;Pooling=true;Min Pool Size=1;integrated security=True;user instance=True;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

every now and then when I run the program I come across with this error message:

See the end of this message for details on invoking 
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.Data.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following:  the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.PoolCreateRequest(Object state)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
   --- End of inner exception stack trace ---
   at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
   at System.Data.EntityClient.EntityConnection.Open()
   at System.Data.Objects.ObjectContext.EnsureConnection()
   at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at PhoneBook.Repository.Departments()
   at PhoneBook.MainForm.PrepareDepartmentComboBox()
   at PhoneBook.MainForm.RefreshForm()
   at PhoneBook.MainForm.MainForm_Load(Object sender, EventArgs e)
   at System.Windows.Forms.Form.OnLoad(EventArgs e)
   at System.Windows.Forms.Form.OnCreateControl()
   at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
   at System.Windows.Forms.Control.CreateControl()
   at System.Windows.Forms.Control.WmShowWindow(Message& m)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.Form.WmShowWindow(Message& m)
   at System.Windows.Forms.Form.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


************** Loaded Assemblies **************
mscorlib
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.431 (RTMLDR.030319-4300)
    CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v4.0.30319/mscorlib.dll
----------------------------------------
PhoneBook
    Assembly Version: 1.0.0.0
    Win32 Version: 1.0.0.0
    CodeBase: file:///C:/Users/a.bayati/AppData/Local/Apps/2.0/YBJD9T4C.VJZ/Y5PPBG2C.8EM/phon..tion_db7e335bcace4eed_0001.0000_08983db023885656/PhoneBook.exe
----------------------------------------
System.Windows.Forms
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.1 built by: RTMRel
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System.Drawing
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.1 built by: RTMRel
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
System
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.431 built by: RTMLDR
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Data.Entity
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.1 built by: RTMRel
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Data.Entity/v4.0_4.0.0.0__b77a5c561934e089/System.Data.Entity.dll
----------------------------------------
System.Core
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.431 built by: RTMLDR
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll
----------------------------------------
System.Data
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.1 (RTMRel.030319-0100)
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_32/System.Data/v4.0_4.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
System.Configuration
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.1 (RTMRel.030319-0100)
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.431 built by: RTMLDR
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Numerics
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.1 built by: RTMRel
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Numerics/v4.0_4.0.0.0__b77a5c561934e089/System.Numerics.dll
----------------------------------------
System.Data.OracleClient
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.1 (RTMRel.030319-0100)
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_32/System.Data.OracleClient/v4.0_4.0.0.0__b77a5c561934e089/System.Data.OracleClient.dll
----------------------------------------
System.Web
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.431 built by: RTMLDR
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_32/System.Web/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Web.dll
----------------------------------------
Microsoft.SqlServer.SmoExtended
    Assembly Version: 10.0.0.0
    Win32 Version: 10.50.2500.0 ((KJ_PCU_Main).110617-0026 )
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.SqlServer.SmoExtended/10.0.0.0__89845dcd8080cc91/Microsoft.SqlServer.SmoExtended.dll
----------------------------------------
Microsoft.SqlServer.ConnectionInfo
    Assembly Version: 10.0.0.0
    Win32 Version: 10.50.2500.0 ((KJ_PCU_Main).110617-0026 )
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.SqlServer.ConnectionInfo/10.0.0.0__89845dcd8080cc91/Microsoft.SqlServer.ConnectionInfo.dll
----------------------------------------
Microsoft.SqlServer.Smo
    Assembly Version: 10.0.0.0
    Win32 Version: 10.50.2500.0 ((KJ_PCU_Main).110617-0026 )
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.SqlServer.Smo/10.0.0.0__89845dcd8080cc91/Microsoft.SqlServer.Smo.dll
----------------------------------------
System.Xml.Linq
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.1 built by: RTMRel
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml.Linq/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.Linq.dll
----------------------------------------
System.Runtime.Serialization
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.1 (RTMRel.030319-0100)
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Runtime.Serialization/v4.0_4.0.0.0__b77a5c561934e089/System.Runtime.Serialization.dll
----------------------------------------
System.Transactions
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.1 (RTMRel.030319-0100)
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_32/System.Transactions/v4.0_4.0.0.0__b77a5c561934e089/System.Transactions.dll
----------------------------------------
System.EnterpriseServices
    Assembly Version: 4.0.0.0
    Win32 Version: 4.0.30319.1 (RTMRel.030319-0100)
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_32/System.EnterpriseServices/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll
----------------------------------------

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
    <system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

and if I quit and run it again there will be no error and the application shows up normally. This problem sometimes occurs at the beginning of the program and sometimes it doesn't. What should I do?

Answer 1

Hello,

Just a question : why , in your connection string , have you user instance = true ? Are you trying to backup/restore a user instance database ?

I suppose that your SQL Server instance is a SQL Server Express 2008 R2 ( version 10.50 ). What is the last installed service pack (SP1 i suppose ) ?

Avoid the System.Data.EntityClient library with SMO . To create a Server object, create before a ServerConnection object, itself created from a SqlConnection object.

If my memory is good, user instance is not a friend of SMO , more exactly, SMO and user instances are living (unluckily) unhappily.

I will do some search about this problem, especially how to backup/restore user instances databases. A little track : Mike Wachal has posted about this topic in the sticky posts at the beginning of the posts list of the SQL Server Express Forum  and also in the SQL Server Express Team Weblog http://blogs.msdn.com/b/sqlexpress/

http://blogs.msdn.com/b/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx

for restore/backup databases :

http://blogs.msdn.com/b/sqlexpress/archive/2007/03/20/backup-and-restore-with-user-instances.aspx

Mike was a man who has the talent to explain in a simple and clear way notions which are not for beginners on the contrary of an preconceived idea.

If you want , a moderator may move your thread towards the SQL Server Express Forum where specialists of user instances are answering. But be careful, SQL Server 2012 (ex Denali ) will be the last version supporting the user instance feature. Moreover, this feature is supported only by SQL Server Express, it is not supported by the Web,Workgroup,Standard,Entreprise,... editions ( so you will have problems if you have to upgrade to a not-free edition because of the limitation of size 10 GB since 2008 R2 )

We are waiting for your feedback to try to help you more efficiently.

Have a nice day

PS : the code for the open of the connection and your backup/restore could be useful to understand your problem

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

Hi and thanks for your reply

Yes, my instance is SQL Express 2008 R2 as you said. And I want to give the user the ability of backup/restore his/her own database which is located in his system. that's why I have used user instance = true. Am I doing wrong?

here is the backup/restore code:

static readonly string strConnection = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|PhoneBook.mdf;Integrated Security=True;User Instance=True;";
public static void BackupDatabase(string destinationPath)
        {
            Backup sqlBackup = new Backup();

            sqlBackup.Action = BackupActionType.Database;
            sqlBackup.BackupSetDescription = "ArchiveDataBase:" + DateTime.Now.ToShortDateString();
            sqlBackup.BackupSetName = "Archive";
            sqlBackup.Database = "PhoneBook";

            BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);
            SqlConnection con = new SqlConnection(strConnection);
            con.Open();
            ServerConnection connection = new ServerConnection(con);
            Server sqlServer = new Server(connection);

            sqlBackup.Initialize = true;
            sqlBackup.Checksum = true;
            sqlBackup.ContinueAfterError = true;
            sqlBackup.Devices.Add(deviceItem);
            sqlBackup.Database = con.Database;
            sqlBackup.Action = BackupActionType.Database;
            sqlBackup.Incremental = false;
            sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
            sqlBackup.FormatMedia = false;
            sqlBackup.SqlBackup(sqlServer);
        }

        public static void RestoreBackup(string Address)
        {
            using (SqlConnection conn = new SqlConnection(strConnection))
            {
                conn.Open();
                ServerConnection sc = new ServerConnection(conn);
                Server srv = new Server(sc);
                BackupDeviceItem bdi = new BackupDeviceItem(Address, DeviceType.File);
                Restore resDB = new Restore();

                resDB.Devices.Add(bdi);
                resDB.NoRecovery = false;
                resDB.ReplaceDatabase = true;
                resDB.Database = conn.Database;

                srv.KillAllProcesses(conn.Database);
                resDB.SqlRestore(srv);
            }
        }

I also searched a lot and come across with some ideas like not using Named Pipes. Is this can be a solution to my problem?

another question. Why this problem occurs occasionally?

Answer 3

Hello,

As you are using user instances, the databases are always local ( it is impossible to connect an user instance in the remote mode ), why are you using the named pipes ? Have you tried the shared memory ( the best way for local databases ) ?

Knowing all the problems generated by the user instances, i have decided to ignore this feature. On all my Express instances, i have disabled this feature.If i have to install a database, 2 solutions:

- i copy the .mdf and .ldf files in the same directory as this used by the system databases and i attach the database thru SMO or T-SQL statements

- i use a program using SMO to create the database and to fill essential tables

To backup/restore your database, you can use SMO code or T-SQL statements in your application.

Another point : the SQL Server Express Edition is annoying because it is the only one edition which has AUTOCLOSE = true and AUTOSHRINK = true as default.Always set these properties to false. The problems caused by AUTOCLOSE are explained in the link i gave you in my 1st reply.

For autoshrink, see

http://blogs.msdn.com/b/psssql/archive/2009/05/21/how-a-log-file-structure-can-affect-database-recovery-time.aspx

http://blogs.msdn.com/b/sqlcat/archive/2008/01/11/db-autoshirnk-option-may-introduce-high-fragmentation.aspx

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx

Check also the values for grow size for the .mdf and .ldf files.If it is too small, you will have too frequently size growthes which are Windows operations really long, heavy and the time is included in the execution time ( ==> possible commandtimeout ). You have to choose values avoiding growthes more than one per day.

Have you looked at the full error message ? It is possible that the computer is too busy or having not enough memory.

I have forgotten : when you are doing a backup or restore, the database is unavailable.These operations must be done with no connected user.The only open connection must be reserved to the backup/restore operation.

Don't hesitate to post again for more help or explanations.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 4

The thing is I got this error message (sometimes no always) at the beginning of the program (when backup/restore methods have not been called yet). backup/restore part of the program works fine with no error. Besides I don't want to use Named pipes but after con.Open() statement the server name changes automatically.

Answer 5

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/6c9c646b-6a5a-45d7-b4e8-2c59a6c7ea03