LCK_M_SCH_S, the schema stability lock caused my stored proc to waiting forever. Is there any way to avoid or got over it?

4

2 Answers

LCK_M_SCH_S is a schema stability lock. This lock is used when queries are being compiled and executed. Lengthy blocking by this type of lock suggest you have a long running query in one or more of your databases. Other than batches these long running queries there is no easy way to get around this type of lock.

3

Sorry , My Question was not Clear, But I've got the Solution, inspired by Jaco de Groot's Answer This lock was due to another Stored proc of masterdb. sp_helpconstraint ,used to find constraints on tables in current database. I used this in my Proc, to get Constraints Details on tables/views I was applying this on View, which caused LCK_M_SCH_S, as I found. Before applying this Proc, I tested for the object(whether type is 'U' or not), It works fine.

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.