System SPID 17 blocks after SSIS Load

  • I'm trying to wrap a TRUNCATE TABLE and a load of 14.7M rows in a single SSIS component. (I've already adjusted the TransactionOption=Required, and VerifyExternalMetaData=False, and set up MSDTC.)

    At the end of the 22 minute load, the SSIS package reports that the load failed ("The transaction has already been aborted"), and then SPID #17 kicks into gear, with command = "TASK MANAGER", and blocking queries. Try as I might, I can't get any insight into what this system spid is actually doing.

    At first I suspected an Auto Stat Update, so I turned off Auto Stats Updates.

    I thought it might be the rollback of the load, but I think rollbacks are under the original SPID, and actually report as "ROLLBACK".

    (Obviously, I'd like to know why the package is failing, but I haven't given up on figuring that out on my own. However, having to sit through 60-120 minutes of this mystery blocker prevents me from making quick progress, so I'd like to know the answer here).

    Thanks in advance for any insight!

  • I'm 90% confident that the SPID was doing a rollback of the MS DTC transaction.

    Normally, you'd see "rollback" as the status of the SPID that was doing the failed query. However, since this was MS DTC, I believe the behavior is a little different.

    I switched to a MSDTC-free transaction model (http://www.mssqltips.com/sqlservertip/3072/sql-server-integration-services-ssis-transactions-without-msdtc/), and it behaves more like we're used to seeing as DBAs (and, bonus: It didn't fail!)

    Thanks for viewing...

  • Good info. Thanks for posting back with your resolution.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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