Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

System SPID 17 blocks after SSIS Load Expand / Collapse
Author
Message
Posted Monday, August 11, 2014 2:15 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 15, 2014 9:31 AM
Points: 78, Visits: 310
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!
Post #1602054
Posted Tuesday, August 12, 2014 10:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 15, 2014 9:31 AM
Points: 78, Visits: 310
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...
Post #1602369
Posted Tuesday, August 12, 2014 10:49 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 18,064, Visits: 16,108
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
Post #1602376
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse