Post reply

Package consistently blocked by SPID -2

  • December 15, 2009 at 1:32 am

    #140616

    I was running a lengthy ETL SSIS package in SQL Server 2008 and aborted it by pressing the Stop button. It was taking so long to abort so I closed the package Task via Windows Task Manager (I know, this was not a wise move). Since then, one of the nested packages consistently gets suspended and a sp_who2 shows that it is being blocked by one SPID -2. Using the query:

    select req_transactionUoW as [UoW ID]

    from syslockinfo where req_spid = -2

    I see 3 rows, the top two UOW are all zeroes, the 3rd is a valid UOW. I can kill the 3rd UOW but running the package again will produce another SPID -2 to kill. When killed, the error produced is: "component <OLE DB Source> failed the pre-execute phase and returned error code 0xC020801C." which makes me think there is a lock on my source table. However, 'DBCC opentran' (run on either the source or destination database) says there are no open active transactions.

    What is locking the resource? Are some transactions invisible to the UOW query?

    I'm not sure if this is related, but when I run another package that uses the same source table, I get these errors:

    SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.".

    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.".

    It is possible that this is a different issue with this package since this is its first run on this server (it was run successfully in the dev server). If this is the same, why didn't it get suspended like the previous one? How do I get past this error?

    Thanks so much for any help.

    Update: Restarting the SQL Server Service fixed the problem.

  • December 17, 2009 at 8:51 am

    #1093549

    Check the transaction options of your package/container etc. Sounds like in your case, transaction is started and then went into lock or something.

    for more details regarding transactions http://msdn.microsoft.com/en-us/library/ms137690.aspx

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply