SQL LocalDb Automatic Instance Startup Failure when called from Visual Studio 2013, but not SQL Server Management Studio

Dave Boal picture Dave Boal · Dec 20, 2014 · Viewed 10.7k times · Source

Per MSDN Docs: http://msdn.microsoft.com/en-us/library/hh510202.aspx

LocalDB supports two kinds of instances: Automatic instances and Named instances.

I suspect this has something to do with my problem, so I am wondering if anyone knows how something like this gets automatically created. If I can quote from the docs, "One automatic instance of LocalDB exists for every version of LocalDB installed on the user’s computer."

Here is a copy of the relevant section in the above link:

Automatic instances of LocalDB are public. They are created and managed automatically for the user and can be used by any application. One automatic instance of LocalDB exists for every version of LocalDB installed on the user’s computer. Automatic instances of LocalDB provide seamless instance management. There is no need to create the instance; it just works. This allows for easy application installation and migration to a different computer.

Different versions of LocalDB will have different instance naming conventions:

SQL 2012 LocalDB = V11.0
SQL 2014 LocalDB = ProjectsV12
  • I've seen others.

As long as the connecting app's connection string points to the correct instance, all is well:

(localdb)\V11.0
(localdb)\ProjectsV12

If I try to connect with SQL Server Management Studio to either instance (localdb)\V11.0 or (localdb)\ProjectsV12, I CAN, the "stopped" server "autostarts".

If I set the SQL Server instance in VS2013 to either instance (localdb)\V11.0 or (localdb)\ProjectsV12, I CANNOT, the "stopped" server "fails" to start. It attempts to start, but fails.

Here is the error message found in the instance error.log indicating why the instance start failed.

014-12-19 15:12:14.09 Logon       Error: 17828, Severity: 20, State: 3.
2014-12-19 15:12:14.09 Logon      The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: <named pipe>]

I may have found a clue here:

On one of my machines where Automatic Instancing works, the sqllocaldb command outputs following:

C:\>sqllocaldb info v11.0
Name:               v11.0
Version:            11.0.3000.0
Shared name:
Owner:              AM\Z617699
Auto-create:        Yes            <-- Yes? - and I have no idea how this is set.
State:              Stopped
Last start time:    12/18/2014 5:18:46 PM
Instance pipe name:

On one of my other machines where Automatic Instancing does NOT work, the sqllocaldb command outputs following:

C:\>sqllocaldb info v11.0
Name:               v11.0
Version:            11.0.3000.0
Shared name:
Owner:              AM\Z617699
Auto-create:        No              <-- No? - and I have no idea how this is set.
State:              Stopped
Last start time:    12/18/2014 5:18:46 PM
Instance pipe name:

I have spent days trying to find the answer to this question. Here is a link to an MSDN forum post that outlines all the unsuccessful steps I have taken to resolve this issue: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/83ad45d5-15c3-4463-bc0c-6c4899bf947e/localdb-visual-studio-2013-will-not-automatically-start-the-sql-2014-localdb-projectsv12-instance?forum=sqlexpress

The workaround is to just start the instance manually before you start VS2013. I'm just trying to resolve this issue so I can get an "Automatic Instance" image for all our developers.

I hope you just know he answer. :-)

Thanks, Dave

Answer

Yair M picture Yair M · Dec 25, 2014

I had the same issue. I resolved it by deleting and recreating the instance from command line:

  1. Open the command line
  2. Delete the session by typing: sqllocaldb delete "v11.0"
  3. Recreate the instance with: sqllocaldb create "v11.0"

The new instance allows auto creation, and solves the issue.

The solution was taken from: http://answers.flyppdevportal.com/categories/sqlserver/sqlexpress.aspx?ID=8bcb5f1e-0240-4df3-8a5e-7e3e73e1c45b