2315877 - Intermittent Deadlocks in Microsoft SQL Server 2014 When Dropping and Recreating Template Tables - SAP Data Services 4.x

• After upgrading to Microsoft SQL Server 2014, SAP Data Services jobs that drop and recreate target tables may experience intermittent deadlocks.
• Job execution fails with an error message similar to:
  "error message for operation <SQLColumns>: <[Microsoft][SQL Server Native Client 11.0][SQL Server]Transaction (Process ID XXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
• The SQL Server Deadlock Report shows the deadlocking process as:
  "procname="mssqlsystemresource.sys.sp_columns_100"
• Two or more tables that are being dropped and recreated at time of deadlock.
• The issue may occur when:
  • Data Transfer transform is creating a temporary table behind the scenes.
  • Data flow uses multiple template tables as targets.

• SAP Data Services 4.x
• Microsoft SQL Server 2014

SQL Server 2014, deadlock, template tables, cardinality estimator, SQL Server 2016 , KBA , EIM-DS , Data Services , EIM-DS-EXE , Job Execution , Problem