Deadlock Issue during Data Flow Task-Execution

Hi, folks!

I got a serious problem with an SSIS-Import. My packages import from a foreign source into a kind of temp-table (actually it's not a temporary table, it′s just filled with data and truncated after completion of the package), do some transformations and then I got a data flow task that simply copies all the rows from the "temp" to the final table. I get the following errors (here there are two simultanious copy operations from two different "temps" into the same final table.

Error: 0xC0202009 at _temp to finaltable 5 2 1, OLE DB Destination [16]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

Error: 0xC0209029 at _temp to finaltable 5 2 1, OLE DB Destination [16]: The "input "OLE DB Destination Input" (29)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at _temp to finaltable 5 2 1, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (16) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC0047021 at _temp to finaltable 5 2 1, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Error: 0xC02020C4 at _temp to finaltable 5 2 1, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047038 at _temp to finaltable 5 2 1, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Error: 0xC0047021 at _temp to finaltable 5 2 1, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Information: 0x40043008 at _temp to finaltable 5 2 1, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x402090DF at _temp to finaltable 5 2 1, OLE DB Destination [16]: The final commit for the data insertion has started.

Information: 0x402090E0 at _temp to finaltable 5 2 1, OLE DB Destination [16]: The final commit for the data insertion has ended.

Information: 0x40043009 at _temp to finaltable 5 2 1, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at _temp to finaltable 5 2 1, DTS.Pipeline: "component "OLE DB Destination" (16)" wrote 5041 rows.

Task failed: _temp to finaltable 5 2 1

Warning: 0x80019002 at KDStat_alles_412: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Task failed: 412

Warning: 0x80019002 at kdstat_alles_master: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Error: 0xC0202009 at _temp to finaltable 1 2 1, OLE DB Destination [4468]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Transaction (Process ID 82) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

Error: 0xC0209029 at _temp to finaltable 1 2 1, OLE DB Destination [4468]: The "input "OLE DB Destination Input" (4481)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (4481)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at _temp to finaltable 1 2 1, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (4468) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC0047021 at _temp to finaltable 1 2 1, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Error: 0xC02020C4 at _temp to finaltable 1 2 1, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047038 at _temp to finaltable 1 2 1, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Error: 0xC0047021 at _temp to finaltable 1 2 1, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Well, it looks like those two processes simply deadlock each other. But there are 11 Simultanious Data Imports which all go fine. The Issue only occurs at those two. The structure of the packages is exactly the same everywhere.

Is it possible that a previous update-sql query hasen′t committed properly and locks the datasets?

I have seen similar behaviour. Check your OLE DB Destination and make sure Table Lock is switched off. It causes a table lock on your destination - probably for performance reasons. If you do simultaneous copy to dest table, this could be the reason.

According to BOL this is switched OFF by default, but in my experience the default is ON.

Regards,

Pipo

|||

Uuuhm, jepp...

This seems to work. Gotta make a few tests, but yes. Thanks a lot, Pipo!

|||

Hi Pipo1,

I was readong your comment to someone about the Deadlock issue during Data Flow task - Execution.

I am having a similar situation.

I am very very new to SSIS, and how do I find out about the table lock on OLEDB Destination.

Please help!!!

Thanks in tons,

Meena.

|||Meena,
Double click on the OLE DB Destination, and if using fast load, you'll have a table lock option.|||

Thank You Phil.

I got the issue resolved.

On a separate note, I am having some issues with the maintenance plans of backing up my db on sql 2005.

My Maintenance plan backs up the database fine, but the scheduled job fails every single time.

All I get is the error "The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases".

I am doing the following in my maintenance plan:

check db integrity

shrink db

re-organize index

update statistics

backup db

maintenance cleanup

The db uses SIMPLE recovery model.(Earlier it was set to be on FULL recovery model).

Any help is appreciated!

Thanks,

Meena.