2 instances using the same database file ????

Question

2 instances using the same database file ????

OomBoom

Hall of Fame

Points: 3481
More actions
October 5, 2009 at 12:13 am

#72540

Hi All
I am seing something very strange on a server. The previouse DBA / Developer loaded 2 instances (1 default and the other named) of SQL on a server and connected 1 database to both of them (Same file everything). Is this normal, I do not think so. Could anyone think of a reason to do this? Should this be possible?
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

OomBoom Hall of Fame Points: 3481 More actions · Answer 1

I checked the server and all databases is exactly the same. As if they are in a cluster. Even sys databses is the same files.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Sarab_SQLChamp Hall of Fame Points: 3882 More actions · Answer 2

This is not possible.

Run the below mentioned script on both the instances and match the filename and other columns.

select * from sysaltfiles

Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It can be done (attach the DB while the other instance is stopped) but there's no way that the DB can be in use in both instances, SQL takes exclusive file locks, the first to run would grab the files and the second would not be able to open the database.

Check the status of the database on both, double check the file names and locations.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OomBoom Hall of Fame Points: 3481 More actions · Answer 4

I included a screen shot that shows the 2 instances and that they have the same files for master. Is it possible that there is 2 names for the same instance?

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 5

I can't see your screenshot... but it's possible to create an Alias for a server (using the client network utility) so you can refer to the same instance using multiple names.

OomBoom Hall of Fame Points: 3481 More actions · Answer 6

The two instance names are

server\MSSQLSERVER - Default

server\prsvr

Not the server name itselfs. Is this possible with Network??

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 7

tvantonder-992012 (10/5/2009)
Not the server name itselfs. Is this possible with Network??

Yes it's possible.

Run the client network utility from the machine where you are seeing the 2 instances and have a look at the Alias tab.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

tvantonder-992012 (10/5/2009)
The two instance names are
server\MSSQLSERVER - Default
server\prsvr
Not the server name itselfs. Is this possible with Network??

Yes, absolutely.

I've seen a case where one instance could be connected to with either ServerName or ServerName\Instance1 or ServerName\Instance2. They all were the same database engine.

Take a look in services (Start-Control Panel-Administrative tools-Services) and see how many SQL Services are running. Connect to both instances, check the error log. Right at the beginning it will give the process ID (Server process ID is 696.). If it's the same processID, then it's the same SQL engine, just accessible via 2 different names.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OomBoom Hall of Fame Points: 3481 More actions · Answer 9

The client network utility has no aliases setup.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

OomBoom Hall of Fame Points: 3481 More actions · Answer 10

That is exactly right. Both have one process ID. There is 2 instances under Services. Do you know how this happens so I can avoid it in future?

Thanks

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>