Post reply

2 instances using the same database file ????

  • 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.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • October 5, 2009 at 12:30 am

    #1061560

    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.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • October 5, 2009 at 1:08 am

    #1061569

    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

  • October 5, 2009 at 1:17 am

    #1061573

    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

  • October 5, 2009 at 2:42 am

    #1061593

    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.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • October 5, 2009 at 3:41 am

    #1061606

    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.

  • October 5, 2009 at 3:55 am

    #1061611

    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.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • October 5, 2009 at 4:31 am

    #1061621

    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.

  • October 5, 2009 at 5:37 am

    #1061647

    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

  • October 5, 2009 at 6:37 am

    #1061670

    The client network utility has no aliases setup.

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

  • October 5, 2009 at 6:44 am

    #1061676

    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.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

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

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