Deadlock in Dataflow

Hi all,

I have encountered a SQL 2005 deadlock issue while executing dataflow in a SSIS package. The deadlock happens when I have indexed two columns. If I don't have index, deadlock does not happen.

Error: SSIS Error Code DTS_E_OLEDBERROR. 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 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

The above causes the rest of the dataflow execution to be terminated.

What my dataflow does is to extract data from 14 flat files and then insert the records into a single table (no primary key, but with two columns indexed).

Can anyone please advise how I can avoid deadlock with indexes in a table?

Thank you and much appreciated!

Before the data flow, in the control flow, I'd issue an Execute SQL task to disable the indexes on the table. Then after the data flow, I'd re-enable them. Should help with performance as well.|||

Hi Phil,

Thank you very much for your response.

Would you mind tell me how do I disable and re-enable indexes (the SQL command/syntax ?) in a SQL task?

Thanks again.

|||http://msdn2.microsoft.com/en-us/library/ms177406.aspx

To disable:
ALTER INDEX your_index_name ON your_table_name DISABLE

To reenable:
ALTER INDEX your_index_name ON your_table_name REBUILD|||

I tried the ALTER INDEX ... DISABLE command and also in SQL Server Management Studio manually right-clicked INDEX folder to "disable all" index, but they didn't seem to disable the index. Deadlock still occurs.

Did I miss something?

Thanks!

|||

I think I got this deadlock solved.

Not sure why disabling index does not work for me, but instead of disabling it, I drop the index before the data load. After finishing data loading, I re-create the index and rebuild it. It works fine that way.

Thanks Phil!

Deadlock & probably other problem

Hi,

We are having sql server 7. But recently when we executing a batch of SQL in that some updates were there & those updates causes triggers needs to be fired. But batch gets teminated in between. We checked the error logs & found that Few other processes are running continously like SPid 14 etc which are opening & closing the datafiles for pubs, & northwind database. Why? Is this a server settings problem Or Virus?

For deadlock is nested triggeres becomes problem after 3-4 level deep on multiple tables?

ThanksThere are a number of things which can make deadlocks more frequent.
One is long running transactions. Triggers cause the transaction to extend for the length of the trigger and also make the actual update less likely to be efficient so can cause the problem - nested triggers even more so.

>> opening & closing the datafiles
Which version do you have? It sounds like these databases are set to autoclose which is a bit odd - I would have said maybe it's a checkpoint but spid 14 doesn't sound like a system spid. Do you have anything monitoring databases or maybe backup software.

Try running this sp
http://www.nigelrivett.net/sp_nrSpidByStatus.html
it will tell you what the last statement executed by a spid is.
You could also use the profiler to see what is happenning but that will also slow down the system.|||Hi
Thanks For Reply.
For Triggers i reduced the transactions time & batch size.

For opening closing datafile the content in log files were like this

2003-07-08 08:39:08.00 spid33 Starting up database 'Northwind'.
2003-07-08 08:39:08.00 spid33 Opening file d:\SQLData\DATA\northwnd.mdf.
2003-07-08 08:39:08.12 spid33 Opening file d:\SQLData\DATA\northwnd.ldf.
2003-07-08 08:39:08.39 spid33 Closing file d:\SQLData\DATA\northwnd.mdf.
2003-07-08 08:39:08.43 spid33 Closing file d:\SQLData\DATA\northwnd.ldf.
2003-07-08 08:39:08.45 spid33 Starting up database 'pubs'.
2003-07-08 08:39:08.45 spid33 Opening file d:\SQLData\DATA\pubs.mdf.
2003-07-08 08:39:08.46 spid33 Opening file d:\SQLData\DATA\pubs_log.ldf.
2003-07-08 08:39:08.62 spid33 Closing file d:\SQLData\DATA\pubs.mdf.
2003-07-08 08:39:08.65 spid33 Closing file d:\SQLData\DATA\pubs_log.ldf.

This happening continously. In this case the SPID is 33.

Thanks|||If you run the following command in Query Analyzer:

sp_dboption pubs

Does the output include AutoClose?|||Just set the databases to not autoclose and it should get rid of that.

Use profiler to log any accesses to those databases and you will see why it's happenning.

Sounds like there is some monitoring going on somewhere - maybe someone keeps clicking on things in enterprise manager or has a gui which tries to get info from the databases.

DDL changes explode the merge engine

You have to be extremely careful with the DDL scripts that you are executing if you are propagating schema changes through the merge engine. The gory details are all wrapped up out here: http://www.mssqlserver.com/replication/alert_merge_ddl.asp

You can view and vote on this issue which has been posted on Microsoft Connect at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299206

http://msdn2.microsoft.com/en-us/library/ms151870.aspx

"If the schema change references objects or constraints existing on the
Publisher but not on the Subscriber, the schema change will succeed on the
Publisher but will fail on the Subscriber."

In your particular case it fails as your publication the first thing that
the merge agent does is apply the schema changes and breaks.

Note further that Microsoft has add the following features.

1) the ability to cancel the schema changes with the following proc -
sp_markpendingschemachange
2) this "new option in SQL Server 2005 that allows you to upload changes
first and then reinit" has been around since SQL 2000.

In your case you will have to locate the row in
select *from sysmergeschemachange

and the delete it as follows, delete the offending row from

delete from sysmergeschemachange where schematext like '%fk_table2totable1%'

This will allow you to do the reinitialization. Note further that you don't
have to do the reinitialization, you can just remove this row and
replication will continue.|||

1. No, sp_markschemachange does NOT work in this case.

2. No. Deleting the row from the table does NOT work either, because it introduces a gap in the schemaversion sequence that creates problems in other areas of the engine. And doing direct modifications to the merge metadata is completely unsupported, so you had better have a support case open and do this under the direction of PSS

DDL changes explode the merge engine

You have to be extremely careful with the DDL scripts that you are executing if you are propagating schema changes through the merge engine. The gory details are all wrapped up out here: http://www.mssqlserver.com/replication/alert_merge_ddl.asp

You can view and vote on this issue which has been posted on Microsoft Connect at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299206

http://msdn2.microsoft.com/en-us/library/ms151870.aspx

"If the schema change references objects or constraints existing on the
Publisher but not on the Subscriber, the schema change will succeed on the
Publisher but will fail on the Subscriber."

In your particular case it fails as your publication the first thing that
the merge agent does is apply the schema changes and breaks.

Note further that Microsoft has add the following features.

1) the ability to cancel the schema changes with the following proc -
sp_markpendingschemachange
2) this "new option in SQL Server 2005 that allows you to upload changes
first and then reinit" has been around since SQL 2000.

In your case you will have to locate the row in
select *from sysmergeschemachange

and the delete it as follows, delete the offending row from

delete from sysmergeschemachange where schematext like '%fk_table2totable1%'

This will allow you to do the reinitialization. Note further that you don't
have to do the reinitialization, you can just remove this row and
replication will continue.|||

1. No, sp_markschemachange does NOT work in this case.

2. No. Deleting the row from the table does NOT work either, because it introduces a gap in the schemaversion sequence that creates problems in other areas of the engine. And doing direct modifications to the merge metadata is completely unsupported, so you had better have a support case open and do this under the direction of PSS