Blocking: Wait Type lck_m_sch_s

Question

Hello,

I have a problem with running such SSIS package:

I have a Sequence Container with TransactionOption set to Required.

I have two data flow tasks in it.

First task takes data from remote server remote table and inserts data to local server, local table.

Second task reads local table for newly downloaded data, deletes corresponding record on remote table, and marks local table records as complete.

Unfortunately, it locks with mentioned wait type.

What am I doing wrong?

Thanks.

Answer 1

SCH_S shouldn't block anything unless you are trying to truncate a table or do DDL operations like add a column.

See the Schema Locks section: http://technet.microsoft.com/en-us/library/ms175519.aspx

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 2

perhaps, but it just hangs on the update step, with blocked by session id showing -2..

Answer 3

Frankly, I have no idea why would it do such a lock, since I'm not really doing anything related to schema change.

And strange thing is, it used to work just fine a couple of days ago, but now it just hangs :(

Answer 4

I managed to solve it by unchecking "table lock" on the first (Insert) OLE destination. But question is - why does it hurt in this case?

Answer 5

I managed to solve it by unchecking "table lock" on the first (Insert) OLE destination. But question is - why does it hurt in this case?

Perhaps because the transaction hasn't been committed yet, as a result of your TransactionOption set to required.  The sequence container has to complete in order to commit the data, I believe.  So, the bulk insert (the fast load option with table lock checked) acquired a bulk insert lock on the table, likely, and will hold on to it until the transaction is done.  At least those are my thoughts.

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 6

but should the bulk complete (and it does so in seconds), it should continue on, right? but for some reason it just hangs forever..