sql local db instance doesn't exist

Question

1. I created an instance with Local System Account successfully. Then I checked the instance information with "info" command, all information of instance was shown. But after a few seconds I run "info" command again, the instance was deleted. Why was the instance deleted? localdb instance was deleted
2. I have a window service run under Local System Account. This service can connect to SQL Local DB by using "MSSQLLocalDB" instance , but could not connect by using a private instance although the private instance was shared. Could everyone give me an advice to solve this problem, please?

Dapplegray

Answer 1

General Observation

LocalDB instances created by Local System are problematic. Refer the following from the MS doc for SQL LocalDB:

I have encountered both of the problems you mention. I have found workarounds for these and many other related issues, but it has been a painful process. If I had to start over again, I would heed the Microsoft advice and avoid instances owned by Local System, or use something other than SQL LocalDB.

Caveats:

• The information below applies only to instances owned by Local System. The file paths, registry entries, and caveats below are different for instances owned by normal Windows accounts.
• Some paths may be different when running in 32-bit Windows--I only use 64-bit Windows.

Answer to question 1

Most likely, your instance has been corrupted, not deleted. Try to create the instance again. If creation is unsuccessful, the existing corrupt instance is preventing re-creation, deletion, and connection. Such corruption can occur to instances created by the Local System account in a 64-bit process, but subsequently accessed in a 32-bit process also running as Local System (or vice-versa).

The only way I've found to completely remove the corrupted instance is to

1. Delete the folder for the corrupt instance from either here (if created in a 64-bit process):

   • C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\{instance name}

     or here (if created in a 32-bit process):

   • C:\Windows\SysWOW64\config\systemprofile\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\{instance name}

2. And remove the associated registry entries.

   • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server Local DB\Shared Instances\{instance name} - (this exists only if you shared the instance)
   • HKEY_USERS\S-1-5-18\Software\Microsoft\Microsoft SQL Server\UserInstances\{GUID} - (choose the GUID which has its DataDirectory path value ending in the instance name)

After all this, when creating the instance again, ensure that you are both creating and accessing the instance from processes having the same "bitness" (32 or 64.) This is tedious and difficult to enforce, unless you own the machine and control all access to it.

Answer to question 2

I have found that the only consistent way to connect to a LocalDB instance owned by Local System via a process running as Local System, is to specify the named-pipe address of the instance in the connection string. I.e.,

Server=np:\\.\pipe\LOCALDB#xxxxxxxx\tsql\query

in place of

Server=(localdb)\.\{instance name}

You can find the named-pipe address of a LocalDB instance with the sqllocaldb.exe tool, using the following syntax:

sqllocaldb info {instance name}

You may have success on different operating systems (32- vs. 64-bit) or different versions of SQL LocalDB, or combination thereof, but I have found consistent success only when using the named-pipe address to connect. Attempting to connect via the instance name can either fail to connect, or even worse, corrupt the instance, as mentioned in the answer to question #2.

Detestable