Job failed with 'QUOTED_IDENTIFIER, ARITHABORT' in SQL server 2000

Recently I faced issue in SQL server 2000. The Database maintenance plan was in place on weekly basis which were doing Update Statistics on the specified database. The Update Statistics job was failing everyday. When I checked the history of the job I found below error:

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.

The solution is to add a parameter in the Job:

-SupportComputedColumn: This parameter is the new feature that was introduced in SQL server 2000 Service pack4(SP4).

Add this parameter in the Job like below and excute the job, it will successfully execute.

EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt "Location of the output file" -WriteHistory  -UpdOptiStats 10 -SupportComputedColumn'

The description for Event ID '1073741877' in Source 'SQLAgent$Name' cannot be found.The following information is part of the event:'[sqagtres] LooksAlive request.

Recently I applied service pack 2 in one of the cluster environment which is SQL server 2008 R2. After applying SP2, few information logs was generating on 2008 SQL Servers which was related to SQL Agent service, these logs were filling up in system application event logs in all the SQL Server nodes.

I was getting below information message in application log:

Message

The description for Event ID '1073741877' in Source 'SQLAgent$Name' cannot be found.  The local computer may not have the necessary registry information or message DLL files to display the message, or you may not have permission to access them.  The following information is part of the event:'[sqagtres] LooksAlive request.

'

Message

The description for Event ID '1073741877' in Source 'SQLAgent$Name' cannot be found.  The local computer may not have the necessary registry information or message DLL files to display the message, or you may not have permission to access them.  The following information is part of the event:'[sqagtres] CheckServiceAlive: returning TRUE (success).

As per Microsoft articles:

The issue occurs because of a code defect in the Sqagtres.dll SQL Server Agent resource file. The information that is mentioned in the "Symptoms" section should not be logged.

The resolution for this bug has been fixed in Cumulative update 3. So if anyone is getting this informational message in application log, apply Cumulative update 3 (http://support.microsoft.com/kb/2754552).

For more information you can visit to Microsoft link:

http://support.microsoft.com/kb/2718920

How to apply SQL service pack or CU in SQL server 2005 Cluster

Here I will explain how to apply Service pack or CU in SQL server 2005 cluster.
Lets assume there are two nodes with Active/Active and SQL is rnning on both the nodes
Follow below steps to apply service pack or CU.

      1. Make sure media is available in C drive on both nodes .

      2. Make sure all SQL instances are running on the same node. If it is Active/Active node, failover instance       from one Active node (B) to other Active node (A) to make other node as passive (B).

      3. After failover validate all SQL instances are up and running

      4. Pause passive node (B) in cluster admin.

      5. First apply Service pack/CU on active node (A). Since the passive node (B) is paused, the installer won’t be able to do anything on the passive node.  

      6. Double click on media and follow steps on active node (A)

   

  Note: If you want to apply Service pack or CU only for specific instance, then select only that instance otherwise select all instance to apply patch.

      7. Once the installation is finished, and if it asks for Server reboot

      8.  Resume passive node (B) in cluster admin.

      9.     Fail over all the instances from Active node (A) to passive node (B).

     10. Now Active (A) becomes passive and Passive (B) becomes Active

     11. Reboot passive node (A).

     12.  Now We need to apply patch on current Active node (B)

     13.  Pause Passive node (A) in cluster admin

     14. Double click on media and follow steps on active node (B)

     15. Now the patching is completed on Active node (B).

     16. If reboot is required, then resume Passive node (A) then fail backup all instances from Active node (B) to          Passive node (A).

     17. Now reboot passive node (B).

     18. Once the passive node comes up, connect all SQL instances and verify the SQL Services are DBs and  also check version of all SQL instances.

     19. Now fail back all SQL resources to its intended nodes