DataContext.CreateDatabase() says file already exists - but it doesn't

Question

This might be a Windows 7 issue, but calling

using (var context = new DataClassesDataContext())
{
    if (!context.DatabaseExists())
    {
        context.CreateDatabase();
    }
}

Results in the following error:

System.Data.SqlClient.SqlException was unhandled Message=Database 'C:\Temp\SmallBusinessManager.mdf' already exists. Choose a different database name. Source=.Net SqlClient Data Provider ErrorCode=-2146232060 Class=16 LineNumber=1 Number=1801 Procedure=""
Server=\.\pipe\952FCA9D-B4B6-4C\tsql\query State=3 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Linq.SqlClient.SqlProvider.ExecuteCommand(String command) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.CreateDatabase() at System.Data.Linq.DataContext.CreateDatabase()

However I've deleted the database files. What could be causing this? Are the files really there but invisible somehow? Something to do with junctions?

Answer 1

Grab Process Monitor and you'll be able to see the file io operations and why they fail. I find that this is often the best and quickest way to get to the root of the problem. It's an excellent utility.

Answer 2

Tracking the Error

So I too have been trying to create a file-based database (named C:\y\bjdatamig\IntermediateData.mdf ) by running the DataContext.CreateDatabase() command and got the same error.

I'm on Vista SP2 with SQLExpress 2008 & VS2008, and used Process Monitor as Josh suggested in his answer here. That led me to see SQL Server was writing to its error.log file which in turn revealed the following block of relevant info (entirety provided here in case anybody else sees something useful among the messages):

2010-06-23 17:04:10.50 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2010-06-23 17:04:10.50 Server      Detected 2 CPUs. This is an informational message; no user action is required.
2010-06-23 17:04:10.55 Server      Perfmon counters for resource governor pools and groups failed to initialize and are disabled.
2010-06-23 17:04:10.55 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2010-06-23 17:04:10.65 Server      Node configuration: node 0: CPU mask: 0x00000003 Active CPU mask: 0x00000003. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2010-06-23 17:04:10.69 spid7s      Starting up database 'master'.
2010-06-23 17:04:10.79 spid7s      1 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
2010-06-23 17:04:10.91 spid7s      0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
2010-06-23 17:04:10.91 spid7s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2010-06-23 17:04:11.12 spid7s      Error: 5598, Severity: 16, State: 2.
2010-06-23 17:04:11.12 spid7s      FILESTREAM feature is not supported on user instances.
2010-06-23 17:04:11.12 spid7s      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLEXPRESS'.
2010-06-23 17:04:11.25 spid7s      SQL Trace ID 1 was started by login "sa".
2010-06-23 17:04:11.31 spid7s      Starting up database 'mssqlsystemresource'.
2010-06-23 17:04:11.33 spid7s      The resource database build version is 10.00.2531. This is an informational message only. No user action is required.
2010-06-23 17:04:11.90 spid9s      Starting up database 'model'.
2010-06-23 17:04:11.91 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\95B6C915-3DB7-46\tsql\query ].
2010-06-23 17:04:11.94 Server      Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
2010-06-23 17:04:11.95 spid7s      Server name is 'JOHN-W500\95B6C915-3DB7-46'. This is an informational message only. No user action is required.
2010-06-23 17:04:12.22 spid7s      Starting up database 'msdb'.
2010-06-23 17:04:12.27 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x5, state: 4. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2010-06-23 17:04:12.27 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
2010-06-23 17:04:12.60 spid9s      Clearing tempdb database.
2010-06-23 17:04:15.49 spid9s      Starting up database 'tempdb'.
2010-06-23 17:04:16.27 spid12s     The Service Broker protocol transport is disabled or not configured.
2010-06-23 17:04:16.27 spid12s     The Database Mirroring protocol transport is disabled or not configured.
2010-06-23 17:04:16.36 spid12s     Service Broker manager has started.
2010-06-23 17:04:16.37 spid7s      Recovery is complete. This is an informational message only. No user action is required.
2010-06-23 17:04:17.84 Logon       Error: 15350, Severity: 16, State: 1.
2010-06-23 17:04:17.84 Logon       An attempt to attach an auto-named database for file C:\y\bjdatamig\IntermediateData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
2010-06-23 17:31:29.89 Logon       Error: 15350, Severity: 16, State: 1.
2010-06-23 17:31:29.89 Logon       An attempt to attach an auto-named database for file C:\y\bjdatamig\IntermediateData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
2010-06-23 17:33:07.09 Logon       Error: 15350, Severity: 16, State: 1.
2010-06-23 17:33:07.09 Logon       An attempt to attach an auto-named database for file C:\y\bjdatamig\IntermediateData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
2010-06-23 17:49:53.53 Logon       Error: 15350, Severity: 16, State: 1.
2010-06-23 17:49:53.53 Logon       An attempt to attach an auto-named database for file C:\y\bjdatamig\IntermediateData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
2010-06-23 18:01:02.74 Logon       Error: 15350, Severity: 16, State: 1.
2010-06-23 18:01:02.74 Logon       An attempt to attach an auto-named database for file C:\y\bjdatamig\IntermediateData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
2010-06-23 18:02:50.92 Logon       Error: 15350, Severity: 16, State: 1.
2010-06-23 18:02:50.92 Logon       An attempt to attach an auto-named database for file C:\y\bjdatamig\IntermediateData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
2010-06-23 18:04:06.31 Logon       Error: 15350, Severity: 16, State: 1.
2010-06-23 18:04:06.31 Logon       An attempt to attach an auto-named database for file C:\y\bjdatamig\IntermediateData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
2010-06-23 18:06:41.22 Logon       Error: 15350, Severity: 16, State: 1.
2010-06-23 18:06:41.22 Logon       An attempt to attach an auto-named database for file C:\y\bjdatamig\IntermediateData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

My SQL Log file is at:

C:\Users\John\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log

Importantly, for my situation, it indicates user instances do not support the "feature", seemingly about streaming a file, and of course CreateDatabase is trying to write to the file system ...

2010-06-23 17:04:11.12 spid7s      Error: 5598, Severity: 16, State: 2.
2010-06-23 17:04:11.12 spid7s      FILESTREAM feature is not supported on user instances.

... also my Connection string in APP.CONFIG specifies a "User Instance" ...

<add connectionString="Data Source=JOHN-W500\SQLEXPRESS;AttachDbFilename=C:\y\bjdatamig\IntermediateData.mdf;Integrated Security=True;User Instance=True" name="IntermediateData" providerName="System.Data.SqlClient" />

Solution

I changed my connection string from

;Integrated Security=True;

to

;Integrated Security=False;

The database was created on the file system.

Why did I not have to change the "User Instance" option? Well, I'm unsure. In fact before I started getting this err the code worked, but I haven't tried it in a month - my speculation is maybe a system update or change to SQL Server caused it. So although fixed, it's still a bit of a mystery.