When the instance was running on which node?

Question

Post reply

When the instance was running on which node?

Roust_m

SSCoach

Points: 17410
More actions
November 28, 2010 at 8:31 pm

#157334

Hi,
I've got a pre-production active/active clustered environment with four instances on it. There were some restarts of the first node in the past few weeks, which caused all instances to run on the second node. This presumably caused some performance issues.
Is there a way to find out when each instance was running on which node? I need this to find out if the poor performance and all instances on one node coincided.
Thanks.

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

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

Adiga One Orange Chip Points: 27284 More actions · Answer 1

The System Event Log will have details as to when the node failed over.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 2

Windows Server 2008 Cluster Manager also will bring up the event log related to the "Critical Events" for the cluster. This might be a quicker way to parse through the event log.

Amu SSCommitted Points: 1691 More actions · Answer 3

You can also get it from sysprocesses.

select hostname from sysprocesses where program_name like 'Microsoft® Windows® Operating System%'

Roust_m SSCoach Points: 17410 More actions · Answer 4

Steve-3_5_7_9 (11/29/2010)
Windows Server 2008 Cluster Manager also will bring up the event log related to the "Critical Events" for the cluster. This might be a quicker way to parse through the event log.

I have Windows Server 2003

Roust_m SSCoach Points: 17410 More actions · Answer 5

Amu (11/29/2010)
You can also get it from sysprocesses.
select hostname from sysprocesses where program_name like 'Microsoft® Windows® Operating System%'

I need to be able to tell what happened in the past. Where it is running now I can tell using cluster administrator.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

To find what happened in the past, you will have to comb through event logs.

For going forward, you could implement a process/job that writes that info out to a table for auditing purposes. Every so often throughout the day, you just insert the hostname and timestamp (as an example) into a table.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

You may also see a message in the SQL Server Error log like the following that has the name of the host it is running on. This message should appear as part of the startup messages near the beginning of the log.

"The NETBIOS name of the local node that is running the server is 'MyClusterNode02'. This is an informational message only. No user action is required."

Roust_m SSCoach Points: 17410 More actions · Answer 8

Michael Valentine Jones (11/29/2010)
You may also see a message in the SQL Server Error log like the following that has the name of the host it is running on. This message should appear as part of the startup messages near the beginning of the log.
"The NETBIOS name of the local node that is running the server is 'MyClusterNode02'. This is an informational message only. No user action is required."

Thanks, this is the easiest way.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 9

Thanks Michael. Info. provided by you helped me too:)

Manu