Post reply

In which node the database instances were running after restart

  • January 12, 2011 at 6:44 am

    #245166

    Hi

    We are using four node clustering.

    We have checked that few servers have been restarted on Dec 29th at 2AM on two nodes.

    Now, we want to know where are all the Database instances were running after the restart?

    Thanks

    Sumanta

    Ryan
    //All our dreams can come true, if we have the courage to pursue them//

  • January 12, 2011 at 7:19 am

    #1272353

    The "Owner" column in the Cluster Administrator would tell on which node the resources are located.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • January 12, 2011 at 8:19 am

    #1272400

    We want this for a particular time after restart. We can see the current owner but what about few days back when the server restarted? There may be failover among the nodes.

    Ryan
    //All our dreams can come true, if we have the courage to pursue them//

  • January 12, 2011 at 8:26 am

    #1272403

    You can find historical information in System Event Log.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • January 12, 2011 at 9:29 am

    #1272446

    You can connect to the database instance(s) (by virtual name) and using the SERVERPROPERTY( ) function you can use the ComputerNamePhysicalNetBIOS property. It should tell you node name that that instance of SQL is running on.

    CEWII

  • January 12, 2011 at 10:47 am

    #1272496

    As Adiga and Elliott mentioned, use Even Viewer to find the Historical Failover Info and the following query to get the current node

    select SERVERPROPERTY('IsClustered') as _1_Means_Clustered

    , SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as CurrentNode

    , SERVERPROPERTY('Edition') as Edition

    , SERVERPROPERTY('MachineName') as VirtualName

    , SERVERPROPERTY('InstanceName') as InstanceName

    , SERVERPROPERTY('ServerName') as Virtual_and_InstanceNames

    , SERVERPROPERTY('ProductVersion') as Version

    , SERVERPROPERTY('ProductLevel') as VersionNameWithoutHotfixes

    Thank You,

    Best Regards,

    SQLBuddy

  • January 12, 2011 at 11:13 am

    #1272510

    The following message that shows the node name is written to the SQL Server Error Log everytime SQL Server is restarted.

    The NETBIOS name of the local node that is running the server is 'MyClusterNode1'.

  • January 12, 2011 at 11:47 am

    #1272539

    While I agree it is written in the log, I see no reason to read the log if I can query it directly and know it will match the logged value. Remember, when you switch nodes SQL is stopped and then started on that new node.

    CEWII

  • January 12, 2011 at 1:17 pm

    #1272602

    Elliott Whitlow (1/12/2011)

    While I agree it is written in the log, I see no reason to read the log if I can query it directly and know it will match the logged value. Remember, when you switch nodes SQL is stopped and then started on that new node.

    CEWII

    The OP wanted to be able to look at the history of where it was running, and it is farily easy to use the xp_ReadErrorLog proc to lookup the info.

    -- List available log files

    exec xp_enumerrorlogs 1

    -- Search last 7 log files

    exec xp_ReadErrorLog 0, 1 , 'NETBIOS name of the local node'

    exec xp_ReadErrorLog 1, 1 , 'NETBIOS name of the local node'

    exec xp_ReadErrorLog 2, 1 , 'NETBIOS name of the local node'

    exec xp_ReadErrorLog 3, 1 , 'NETBIOS name of the local node'

    exec xp_ReadErrorLog 4, 1 , 'NETBIOS name of the local node'

    exec xp_ReadErrorLog 5, 1 , 'NETBIOS name of the local node'

    exec xp_ReadErrorLog 6, 1 , 'NETBIOS name of the local node'

Viewing 9 posts - 1 through 8 (of 8 total)

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