MSSQL SERVER agent job- SAP_sid_SID_MSSQL_COLLECTOR

[former_member203438]

Hello All,

Am facing the issue in SAP on MSSQL database SQL server agent jobs.

One of the job failing (SAP_sid_SID_MSSQL_COLLECTOR) frequently and it was scheduled to run every twenty minutes due to this the SAP_COLLECTOR_FOR_PERFMONITOR job failing in sap for every two hours and the strange thing is the job scheduled for every one hour and for one hour its excuting successfully and another hour its failing due to sql agent job.

The job log and sql agent job logs are same as below.

SQL error 512: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,>, >= or when the subquery is used as an expressio

Job cancelled after system exception ERROR_MESSAGE

Message

Executed as user: dea. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. [SQLSTATE 21000] (Error 512). The step failed.

I tried all the notes mentioned below but still the job getting cancelled for every alternate hour.

1834490 - SAP_COLLECTOR_FOR_PERFMONITOR fails with SQL Error 512

1519991 - Error in DBACOCKPIT: Sub query returned more than 1 value

Please help me to resolve this job as soon as possible.

[S_Sriram]

Hi JK.

Could you refer the SAP Note " 2278677 - Subquery returned more than 1 value in Single Table Analysis"

BR

SS

[luisdarui]

Hello,

It is very strange that SAP Note 1519991 didn't resolve your issue. Usually this is the path to the solution in this case.

1. Delete all SQL Agent jobs named after SAP* from your SQL Server Instance (be careful if you have more than one SAP system hosted in the same instance! In this case, delete only the jobs that have the same <SID> in the name).
2. Log on the system, open DBACockpit transaction, go to "Configuration" > SQL Script Maintenance. Execute the script named "sap_zz_create_jobs" for your SQL Server release (2008, 2012, 2014, etc). It will recreate all jobs for your SAP system.
3. As described in the SAP Note 1519991, reset the collector data. replace <DBNAME> with the <SID> and <sch> with the <sid>.

Best Regards,
Luis Darui