Skip to Content
0

SQL Server dealock error when running reports

Aug 28, 2017 at 03:45 PM

73

avatar image
Former Member

Hi,

I'm a junior SQL Server DBA and are trying to resolve an issue for my client in SAP. I believe they are running BI report through Business Objects on reporting server.

The issue is that when they run the report it would complete on first, second, and sometimes third or even fourth attempt and then start to error out (Please see the attached error snapshot). Although the error seems to show that it is deadlock related I believe it is something else e.g. code. Following are the troubleshooting steps I have performed so far:

1. Turned on the SQL Profiler tool and found that there were tonnes of Lock Escalations occurring thus leading to deadlocks.
2. Turned the trace flag 1211 which disabled the lock escalation and rerun the report, but it errors out again on the third attempt of report generation. SQL Profiler does not show any Lock escalation issue this time neither were there any deadlocks.
3. The developers are keep insisting that this is Database issue but there are no dealocks or lock, or even lock escalation found in the database.

This is the query executed by the BO:

SELECT Table__7."EVENT_NBR", Table__18."inc_cat_n", Table__19."inc_cat_n", Table__4."prod_cls_n", Table__7."AST_SERIAL_NBR", Table__1."item_sc", Table__1."supp_ref", Table__1."item_keyb", Table__1."acquired_date", Table__1."expiry_date", Table__1."AST_life", Table__7."CURRENT_STATUS", Table__9."remarks", Table__7."COMPANY_NAME", Table__1."item_sc", Table__7."RES_CODE", Table__7."CURRENT_FLAG", Table__2."item_status_sc", Table__1."item_id", Table__4."product_n", Table__7."ACTUAL_DEFECT_CODE", Table__8."aff_usr_name", Table__8."rep_usr_name", Table__8."incident_ref", Table__7."REP_REMARKS", Table__8."incident_id", Table__8."date_logged", Table__7."REP_COMPLETION_DATETIME", Table__8."inc_resolve_act", Table__8."inc_cat_id", Table__6."inc_cat_n", Table__17."inc_cat_n", Table__1."usr_n", Table__7."ACTUAL_DEFECT_GROUP", Table__7."RESOLUTION_GROUP", Table__7."TRIAGE_DECISION", Table__7."RESOLUTION_DATETIME" FROM "ODS-OLTP"."MLM_SEM_VW"."AST_INCIDENTS" Table__7 RIGHT OUTER JOIN ( SELECT "NEW1"."DBName.dbo"."incident"."incident_id", "NEW1"."DBName.dbo"."incident"."inc_order_id" , "NEW1"."DBName.dbo"."incident"."sup_asi_date", "NEW1"."DBName.dbo"."incident"."csg_id",
"NEW1"."DBName.dbo"."incident"."csg_sc",
"NEW1"."DBName.dbo"."incident"."usr_id",
"NEW1"."DBName.dbo"."incident"."bldng_id",
"NEW1"."DBName.dbo"."incident"."sectn_id", "NEW1"."DBName.dbo"."incident"."rsp_usr_id", "NEW1"."DBName.dbo"."incident"."cause_id" FROM
"NEW1"."DBName.dbo"."incident" ) Table__8 ON (Table__8."usr_ref"=Table__7."ETS_SAP_NOTIFICATION_NBR") INNER JOIN "NEW1"."DBName.dbo"."inc_cat" Table__6 ON (Table__6."inc_cat_id"=Table__8."inc_cat_id") INNER JOIN "NEW1"."DBName.dbo"."inc_data" Table__10 ON (Table__8."incident_id"=Table__10."incident_id") INNER JOIN "NEW1"."DBName.dbo"."inc_cat" Table__17 ON (Table__8."cause_id"=Table__17."inc_cat_id") INNER JOIN "NEW1"."DBName.dbo"."inc_cat_tr" Table__18 ON (Table__8."cause_id"=Table__18."inc_cat_id") INNER JOIN "NEW1"."DBName.dbo"."inc_cat_tr" Table__19 ON (Table__8."inc_cat_id"=Table__19."inc_cat_id") LEFT OUTER JOIN ( SELECT "NEW1"."DBName.dbo"."act_reg"."act_reg_id", "NEW1"."DBName.dbo"."act_reg"."act_type_id", "NEW1"."DBName.dbo"."act_reg"."incident_id", "NEW1"."DBName.dbo"."act_reg"."remarks", MAX("NEW1"."DBName.dbo"."act_reg"."date_actioned") as Max_Date FROM "NEW1"."DBName.dbo"."act_reg" INNER JOIN "NEW1"."DBName.dbo"."act_type" ON "NEW1"."DBName.dbo"."act_reg"."act_type_id" = "NEW1"."DBName.dbo"."act_type"."act_type_id" where "NEW1"."DBName.dbo"."act_type"."act_type_sc" = 'PENDING-CLOSURE' group by "NEW1"."DBName.dbo"."act_reg"."act_reg_id", "NEW1"."DBName.dbo"."act_reg"."act_type_id", "NEW1"."DBName.dbo"."act_reg"."incident_id", "NEW1"."DBName.dbo"."act_reg"."remarks" ) Table__9 ON (Table__9."incident_id"=Table__8."incident_id") INNER JOIN "NEW1"."DBName.dbo"."item" Table__1 ON (Table__1."item_id"=Table__8."item_id") INNER JOIN "NEW1"."DBName.dbo"."sectn_dept" Table__11 ON (Table__1."sectn_dept_id"=Table__11."sectn_dept_id") INNER JOIN "NEW1"."DBName.dbo"."item_status" Table__2 ON (Table__2."item_status_id"=Table__1."item_status_id") INNER JOIN "NEW1"."DBName.dbo"."product" Table__4 ON (Table__1."product_id"=Table__4."product_id") WHERE ( Table__4."prod_cls_sc" not in ( 'MOB SVC CATALOGUE' , 'CONSUMABLE' , 'BATTERY') and Table__1."item_sc" is not null and Table__10."event_type" = 'i' and Table__11."sectn_dept_sc"= 'MML EQMNT-Company Name STOCK' ) AND ( Table__8."date_logged" BETWEEN @Prompt(Today - 30 - Date Logged) AND @Prompt(Today - 1 - Date Logged) AND Table__7."AST_SERIAL_NBR" = @Prompt('Serial # / No de série','A',,Mono,Free,Persistent,,User:2,Optional) AND ( Table__11."sectn_dept_sc" = 'MML EQMNT-Company Name STOCK' and Table__10."event_type" = 'i' ) )

Any input and suggestion is appreciated.

Al

error.png (32.5 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Prithviraj Rajpurohit Aug 29, 2017 at 12:37 PM
0

Hi

Please check the sap note if you haven't yet

https://launchpad.support.sap.com/#/notes/1186745

Regards

Prithviraj.

Share
10 |10000 characters needed characters left characters exceeded
Luis Darui
Sep 04, 2017 at 04:11 PM
0

Update SQL Server to the most current Service Pack and Cumulative update, and see if you still face the same issue.

There were some intra-query parallel deadlocks in the past and were fixed by Microsoft. Make sure to always post your current SQL Server version so we can best help you with.

Share
10 |10000 characters needed characters left characters exceeded