I have a situation that I encountered with one of my clients, that I have never seen before. They are running SQL Server Express 2008 R2. There are two instances of SQL Server installed - a default instance on DATA-SVR, and a named instance on DATA-SVR\Product.

When trying to investigate a problem on the system, I checked the data link files on all workstations, and saw that some users were connecting to DATA-SVR, and others were connecting to DATA-SVR\Product. They were able to do this, because both instances have exactly the same databases. Not good, to say the least.

However, when I checked the database paths between the two instances, they are both pointing at the same data files. I checked that this was the case by creating a table in DATA-SVR, and then accessing the same table from DATA-SVR\Product. They're definitely pointing at the same data, so catastrophic failure was averted. However, to clean things up, I tried detaching the databases from the DATA-SVR\Product instance so that there was only one instance to connect to, but this also detached the database from the default instance.

We have a service running on the server, which for some reason is only able to access the named instance, and not the default instance, so I've now changed all users to use the named instance. Everything is working now. However, I'm still uneasy about how this situation could have come about.

Can anyone shed some light on what is happening here, and how to fix it (preferably without reinstalling the database server)?

  • 3
    I am sure that having the same PHYSICAL file between 2 instances is impossible. Even the database has same names, they cannot have same PHYSICAL file names. Logical file names can be same. Post a screenshot or output of sp_helpdb dbname to verify. As a solution, during a downtime, you can backup and restore database (with move option). You can keep the same name if you want. – Kin Shah Nov 19 '14 at 13:04
  • 1
    when I checked the database paths between the two instances, they are both pointing at the same data files - this is quite simply impossible, unless you mean the data link files or connection strings point to an mdf file using the AttachDbFileName attribute - which means each user creates their own copy of the database. (As an aside, you shouldn't use this "feature".) Why are there two different instances in the first place? – Aaron Bertrand Nov 19 '14 at 13:46
  • 2
    I don't know of any way a db file can be shared between 2 instances. Is it possible one of the names is an alias? open the sql config manager and see if there is a alias set for the instance. – CleanFill Nov 19 '14 at 13:46
  • 2
    I agree with @CleanFill. Can you even prove that there are two different instances? It doesn't sound like there are - it sounds like there is one instance which is likely running on port 1433 and so can be accessible with or without the instance name? Show the SQL Server Configuration Manager's SQL Server Services pane. – Aaron Bertrand Nov 19 '14 at 13:54
  • 2
    Could you execute select @@SERVICENAME on the default instance? This should give the named instance name, if the named instance is also the default instance. So on DATA-SRV this should give PRODUCT as the result. Which would indicate that you are dealing with one instance being accessed with two different connection strings. – Reaces Nov 19 '14 at 14:28

This happened to me once when I restore system database of SQL 2014. When I investigate further, the 2 different name was pointing to same physical file and from @@version I can see both having similar naming. The database which I restored was pointing to another database and it got fixed when I revert back from single user mode to multi user.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.