0

I am loading staging tables using Azure Data Factory. ADF always exclusive-locks the target table

While this is running I also need to run an audit/metadata query to check tables existence. All of the usual methods to check table existence get stuck in a LCK_M_SCH_S on the table.

For example, while I am loading just one table in a database, these queries don't finish - they get stuck in a wait:

This one returns some rows then gets stuck

SELECT S.name TABLE_SCHEMA, T.[name] TableName, 1 As Ex
FROM DB.sys.TABLES T
JOIN DB.sys.schemas S
ON S.schema_id = T.schema_id
WHERE S.name IN ('SCH1','SCH2')

This one just gets stuck

SELECT OBJECT_ID('SCH1.MyTable')

This one doesn't return anything - just gets stuck

SELECT S.name SourceName,T.name Objectname
FROM DB.sys.tables AS T
JOIN DB.sys.schemas S
ON S.schema_id = T.schema_id
WHERE S.name IN ('SCH1','SCH2')

Is there any way to check for a tables existence, even if it has a TABLOCKX on it?

I've tried the old WITH (NOLOCK) - this makes no difference.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • Perhaps you can query the lock, rather than the table. See for example [this](https://stackoverflow.com/questions/694581/how-to-check-which-locks-are-held-on-a-table) – HoneyBadger Sep 11 '19 at 07:29
  • Good idea but it won't help with the tables that aren't playing nice. I think I'll just need to put a long timeout on it. – Nick.Mc Sep 11 '19 at 09:03

0 Answers0