How to find on which node SQL Server instance was running before restart?

 

In situation, where SQL Server instance running on a node is restarted usually after Windows Operating System patching or for some other reason, there may be need to find to which node the SQL Server instance was running on before.

Here is the solution for this;

By default whenever SQL Server instance restarted on Clustered Environment, on SQL Server Error Log it will post the message below;

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

So, making use of the xp_ReadErrorLog we can query the ErrorLog and find the details.

-- To know the list available Error log files exec xp_enumerrorlogs 1 -- Search the available error 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'

DOS Batch file To search string in given file

 

Requirement:

To Search the given files under current directory for given strings. If anything found to be written to a file.

Sample Screen:

 

Main Script: CHK_Error.bat

@echo off set fname= set filename= set yyyymmdd= set hhmm= IF %1. NEQ . (set fname="C:\Accounts\CalWIN\Build\SQLServer\DOS_Batch\%1*.lst") ELSE ( GOTO _Usage ) for /F "tokens=2-4 delims=/ " %%i in ('date /t') do set yyyymmdd=%%k%%i%%j for /F "tokens=1-2 delims=: " %%l in ('time /t') do set hhmm=%%l%%m set filename=Error_%yyyymmdd%%hhmm%.txt echo "Error Log file name is : " %filename% echo "files to be searched : " %fname% findstr /i /n /g:search.txt  %fname% > %filename% if %errorlevel% == 0 (GOTO :_Error_Found ) else  ( Echo  **  No Errors found    ** Goto :END ) :_Error_Found notepad %filename% Goto :END :_Usage echo " Usage is chkerrors.bat <filestring> :END @echo on

Search.txt

msg error severity warning fail fatal

Input Files: file1.lst

dgadagdagad abacdefghijklmnopqrstuvwxyzagadgda gagad st messgae g adgadg agadgadgadadadsflmsvbkobsagad dgadg ad ddad dgadgadg error 121 dgad gdagad gadagad gagad msg 128, 9341353513

Input Files: file2.lst

test messgae g sagad dgadg ad ddad dgadgadg error 121 dgad gdagad gadagad gagad msg 128, 9341353513

Useful Microsoft SQL Server websites on SQL Server

Here, is the list of useful Microsoft SQL Server Websites.

Title	Description / Content	Website
Microsoft SQL Server	Commercial Site Buy, About Pricing, Edition, License Positioning whitepapers Case Study Edition Product Information Customers Partners Support	http://www.microsoft.com/sqlserver/en/us/default.aspx
SQL Server Books On Line ( BOL )	Books On Line - BOL	http://msdn.microsoft.com/en-us/library/ms130214.aspx
MSDN SQL Server Forum	SQL Server Online Community Forum	http://social.msdn.microsoft.com/Forums/en/category/sqlserver/
	SQL Server Training Webinars Community Blogs Learning Resources	http://msdn.microsoft.com/en-us/sqlserver/default.aspx
SQL Server Technet	Troubleshoot an issue Top Support Solutions	http://technet.microsoft.com/en-us/sqlserver/default.aspx
SQL Server Certification	Certification Path Certification Title Courses	http://www.microsoft.com/learning/en/us/certification/cert-sql-server.aspx