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'

Impact of renaming “sa” account on SQL Server 2008

 

Error: 912, Severity: 21, State: 2

Microsoft has confirmed that this is a problem in the Microsoft products that are listed 

Microsoft SQL Server 2008 Workgroup

Microsoft SQL Server 2008 Enterprise

Microsoft SQL Server 2008 Developer

Microsoft SQL Server 2008 Standard

This problem was first corrected in SQL Server 2008 Service Pack 1

Cause:

The upgrade process includes a script to create the SP_Syspolicy_Create_Purge_job stored procedure. The procedure creates the syspolicy_purge_history job and hardcodes the owner of the job to be the sa account. Because the sa account is renamed, the job creation fails.

Workaround:  recreate the sa account

Reference:  FIX: Error message when you try to upgrade an instance of SQL Server 2005 to SQL Server 2008: "Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes"

SQL Server 2008 Service Pack 2 released

 

SQL Server 2008 SP 2 released on 29 Sep 2010.

To download the SQL Server 2008 SP2 release please select from the links below:

• SQL Server 2008 SP2: http://go.microsoft.com/fwlink/?LinkId=196550

• SQL Server 2008 SP2 Express: http://go.microsoft.com/fwlink/?LinkId=196551

• SQL Server 2008 SP2 Feature Packs: http://go.microsoft.com/fwlink/?LinkId=202815

SQL 2000 SP4 Installation Error sp_vupgrade_replication(1)

 

Error

• Msg 170, Level 15, State 1, Server MS, Line 1

Line 1: Incorrect syntax near '-'.

The sqlsp.log file indicates:

Process Exit Code: (0)

Error running script: sp_vupgrade_replication (1)

Action CleanUpInstall:

Installation Failed.

Resolution:
1. Check if distribution database exist though the Replication is dropped
2. If so drop the distribution database
3. To drop the distribution database by over - restoring of some user database
4. Perform the patching / SQL SP again.

SQL2000 SP4 Installation Error sp_vupgrade_replication(1)

 

SQL Server 2005 Patching fails CREATE DATABASE failed.

Error:

Error Number : 29537

Error Description : MSP Error: 29537 SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]CREATE DATABASE failed.
Some file names listed could not be created. Check related errors.. To continue, correct the problem, and then run SQL Server Setup again.

CAUSE:
By default the setup creates the Temp_MS_AgentSigningCertificate_database Database on specified default database location,
If the location doesn't exist, it fails to create the database and the setup fails.

RESOLUTION:
1. In SSMS, go to Server Properties > Database Settings > Database Default Locations ,check default location for Data and Log .

2. Try to access locations specified at Data and Log, Make sure files are available there.
3. If any of the files missing from above location , we hit the above error.
4. In that case, change the path to point to reall location of mdf/ldf files .
5. Setup should now succeed.

1. Reference: a recently applied update, kb955706, failed to install

SQL Server Latest Service Pack and Security Hot fixes

Applies to	Version Range	Type	Description	Target Version	Release Date	Security Bulletins	KB	Download
SQL Server 2000	Below 8.00.2039	Service Pack	SQL Server 2000 SP4	8.00.2039	6-Jun-05	MS03-031	KB884525	http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en
SQL Server 2000	8.00.2039-8.00.2054	GDR	SQL Server 2000 SP4 GDR	8.00.2055	10-Feb-09	MS09-004	KB960082	http://www.microsoft.com/downloads/details.aspx?familyid=d5bb816a-6e1a-47cb-92be-51c565ee184c&displaylang=en
SQL Server 2000	8.00.2148-8.00.2281	QFE	SQL Server 2000 SP4 QFE	8.00.2282	10-Feb-09	MS09-004	KB960083	http://www.microsoft.com/downloads/details.aspx?familyid=A93F3CFE-18C9-4218-A551-13BF415E418A&displaylang=en
SQL Server 2005	Below 9.00.4035	Service Pack	SQL Server 2005 SP3	9.00.4035	15-Dec-08	N/A	KB955706	http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en
SQL Server 2005	9.00.4035-9.00.4052	GDR	SQL Server 2005 SP3 GDR	9.00.4053	12-Oct-09	MS09-062	KB970892	http://www.microsoft.com/downloads/details.aspx?familyid=0d878f4b-71e8-4170-9a14-1bce684811ce&displaylang=en
SQL Server 2005	9.00.4205-9.00.4261	QFE	SQL Server 2005 SP3 QFE	9.00.4262	12-Oct-09	MS09-062	KB970894	http://www.microsoft.com/downloads/details.aspx?familyid=e6f307c1-8b21-406e-9c6f-b1a3a1e9a98f&displaylang=en
SQL Server 2008	Below 10.00.2531	Service Pack	SQL Server 2008 SP1	10.00.2531.00	27-Aug-09	N/A		http://www.microsoft.com/downloads/details.aspx?FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19&displaylang=en
	It lists only the latest SQL Server Service Pack and Security Hot fixes as on 22nd April 2010

SQL Server - Patching : An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

 

An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

 

The SQL Server team is moving away from the current priority-driven hotfix release model to a scheduled delivery model. In the scheduled delivery model, a customer can receive a hotfix to address their most critical situations with a short turn-around time. Additionally, a customer can receive a fix that has undergone more testing and that is released on a schedule basis. Therefore, the SQL Server team has created the following delivery mechanisms.

Critical On Demand - COD	On Demand- OD	Cumulative Update - CU	General Distribution Release - GDR
The hotfix is released on or before a mutually agreed upon date based on the customer's need	The hotfix is released on or before a mutually agreed upon date based on the customer's need	The update is released every 2 months.	A GDR addresses an issue that has a broad customer impact, that has security implications, or that has both. A GDR is determined and issued by Microsoft as appropriate and when appropriate. GDRs are kept to a minimum.
		It contains all previous critical on-demand hotfixes to date.

 

ISM eliminates the need for hotfix rollup releases. The first CU after a new release contains the hotfix rollup, plus any additional QFEs.

A hotfix package does not replace a service pack. A hotfix package is optional. A hotfix package can be installed or uninstalled at any time. Additionally, hotfix packages are cumulative. Therefore, the latest OD hotfix package or CU hotfix package includes all previously released hotfixes.

Quick Fix Engineering - QFEs are used for the majority of fixes where the effects of the problem are not widespread or severe enough to warrant a GDR.