December 13, 2012 at 6:47 am
I have a SSIS package that is truncating and then inserting a whole table from serverA to serverB, however I am trying to view the progress of the job by performing a select with a NOLOCK hint on serverB but it gets lock by the "BULK INSERT" performed by the SSIS package.
I have review the "sys.dm_tran_locks" and found that the "BULK INSERT" is holding a "Sch-M" lock on the table, and I don't understand why.
Please can you help me with this?
Thanks.
December 13, 2012 at 8:01 am
Sch-M locks are schema locks. Means you can't change the definition of the table during the bulk insert. Might mess the whole thing up if you were to remove a column, for example.
I don't think you can "see the progress" on a bulk insert by querying the target table. I've never tried it, so I'm not totally sure, but I'm pretty sure it doesn't work that way. It's not a cursor that inserts one row at a time.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2012 at 8:11 am
GSquared (12/13/2012)
Sch-M locks are schema locks. Means you can't change the definition of the table during the bulk insert.
Sch-M is a schema modification lock. Taken by the bulk insert to allow it to change the definition of the table. Means no queries at all can run while that lock is held as all queries, regardless of isolation level, must take a schema-stability lock (Sch-S), Sch-M blocks any schema stability locks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 13, 2012 at 8:58 am
GilaMonster (12/13/2012)
GSquared (12/13/2012)
Sch-M locks are schema locks. Means you can't change the definition of the table during the bulk insert.Sch-M is a schema modification lock. Taken by the bulk insert to allow it to change the definition of the table. Means no queries at all can run while that lock is held as all queries, regardless of isolation level, must take a schema-stability lock (Sch-S), Sch-M blocks any schema stability locks.
Yep.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply