LCK_M_X Suspended with very high wait time

• StephenF

  SSC Enthusiast

  Points: 124

  More actions

  January 18, 2011 at 11:20 am

  #388660

  Hi All

  We have an SSIS package with tsql to select all records from one DB table where the ID field is not in the other DB table so over night we should only append the new data from the source DB to the destination DB.

  Here is the code

  SELECT lFaultID, sDescription, lPriority, lUserID, dtOpenDateTime, dtClosedDateTime, lFaultTypeID, lFaultSourceID, lSiteID, lSolutionID, lStatusID,

  dtLastLogTime, dtInserted, dtUpdated, bEscalated, dtEscalated, sFaultReference

  FROM firstdb.dbo.tblFault

  where Not firstdb.dbo.tblFault.lFaultID in (Select lFaultID From secdb.dbo.tblReportsFault)

  And Not firstdb.dbo.tblFault.lFaultTypeID in (1001,1002)

  The suspended BULK INSERT says it is blocked by the spid of the above select statement

  We need to do it this way IE only the new rows from the source as some of the tables have over 10 mil rows.

  Our 3rd party software provider said they don’t want me to use replication so I thought this would do the job?

  Any help would be very much appreciated

• StephenF

  SSC Enthusiast

  Points: 124

  More actions

  January 19, 2011 at 6:01 am

  #1275245

  Can any one help pls I have read loads about this and all the insert jobs are now nt running???

• Northern Monkey

  Ten Centuries

  Points: 1017

  More actions

  January 19, 2011 at 10:03 am

  #1275414

  This issue has now been fixed

  Thanks