Unable to Log In to SQL Server Local DB Due to Missing MDF File

Today, seemingly out of the blue, I was unable to connect to my MSSQLLocalDB instance of SQL Server Local DB. The error message I received indicated something went wrong during the login, purportedly with a trigger. Here’s how I did some troubleshooting, research, and finally figured out a non-destructive way to get back in my instance.

The error messages when trying to establish a connection to (localdb)\MSSQLLocalDB were this:

Logon failed for login 'AzureAD\Username' due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english.

In the error log located in the instance’s folder, there were a few extra lines which indicated the real problem:

Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "C:\Users\Username\AppData\Local\Temp\VS11CodeIndex\Microsoft.VsCodeIndex_d42711ec_5a1c_490d_a734_f6126da1cfea.mdf". Operating system error 2: "2(The system cannot find the file specified.)".

I didn’t know Visual Studio Code used Local DB to create some type of code index (there is virtually no documentation available for this). Opening Code didn’t do anything to resolve the problem; the database file didn’t magically re-appear. I’ll mention here that I have no idea how that MDF file was deleted. I can only assume that Code (or an update to Code) caused that to happen.

Some research revealed a few potential solutions, but none worked for various reasons. Not being able to log on restricted a number of solutions I found online, such as:

• Executing sp_detach_db or a DROP DATABASE statement.
• Specifying a valid database in the connection string, even master.

Other attempts that didn’t work were:

• Creating an empty file with the correct name.
  Error message in the log file: The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file 'C:\Users\Username\AppData\Local\Temp\VS11CodeIndex\Microsoft.VsCodeIndex_d42711ec_5a1c_490d_a734_f6126da1cfea.mdf'. Additional messages in the SQL Server error log and operating system error log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
• Copying a valid MDF file without matching the log (LDF) file.
  Error message: One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
• Deleting the LDF file associated with the missing MDF file.
• Using sqlcmd to establish a connection to the master database.

Here’s how I ended up getting back in the instance: I had to give Local DB a valid database file and matching log file to trick it into allowing a connection to be established. Step-by-step, it looks like this:

1. Killing the sqlservr.exe process associated with the instance.
   The sqllocaldb stop MSSQLLocalDB command did not work; it failed with similar error messages in the log
2. Locating valid data and log files from another database.
   I used the master.mdf and mastlog.ldf from another Local DB instance.
3. Copying them to the folder where Local DB expected the missing files to be.
4. Renaming those files to match the names expected.
5. Restarting the Local DB instance: sqllocaldb start MSSQLLocalDB.
6. Dropping the database that caused the problem.

I presume Visual Studio Code will rebuild that code index database at some point, though it hasn’t just happened by opening Code again. I don’t use Code for any significant amount of work though, so it may just not have had a need to create that database.

Along the way, there were a few solutions I thought of but didn’t implement:

• Deleting the MSSQLLocalDB instance and re-creating it. That could have been a quick solution to the problem, but I didn’t want to have to re-attach the databases (or potentially lose them).
• Use a trace flag to enable the Dedicated Admin Connection (DAC). I am not sure if connecting with the DAC would have worked to begin with. Trace flag 7806 should enable the DAC even to a Local DB instance.

In the end, I don’t understand why Local DB refused to let me log on to any database, even master, with that one database file missing. Perhaps there’s some connection string option that would have ignored that, but I am not aware of it.