August 11, 2014 at 2:15 pm
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!
August 12, 2014 at 10:20 am
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...
August 12, 2014 at 10:49 am
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