Early today, I was loading data and wanted to monitor how things are going but unfortunately ran into Schema Block issues ( LCK_M_SCH_S ).
Here is what I received upon running sp_whoIsActive
- Session 52
- Insert into dbo.PaymentAccount
- Session 67
- Ran a query to script out Foreign Keys
- But, ended up blocked by Session 52
- The wait is LCK_M_SCH_S
Thought the problem was trying to access Object Catalog Views such as sys.foreign_keys.
set transaction isolation level read committed; go select top 5 tblFK.parent_object_id from sys.foreign_keys tblFK
set transaction isolation level read uncommitted; go select top 5 tblFK.parent_object_id from sys.foreign_keys tblFK
- Fixed by changing the transaction isolation level to read-uncommitted
Functions also have problems.
Scaler Function – Object_Namme
--Blocked set transaction isolation level read uncommitted; go select parentObject = object_name(parent_object_id) from sys.foreign_keys with (nolock)
set transaction isolation level read uncommitted; go select top 500 parentObject /* object_name(tblFK.parent_object_id) */ = QuoteName(tblFKS.name) + '.' + QuoteName(tblFKP.name) from sys.foreign_keys tblFK with (nolock) inner join sys.objects tblFKP with (nolock) on tblFK.parent_object_id = tblFKP.object_id inner join sys.schemas tblFKS with (nolock) on tblFKP.schema_id = tblFKS.schema_id
- Fixed by replacing scaler function object_name ( and object_schema_name ) with references to actual referenced tables such as sys.object and sys.schemas