Msg 568, Level 16, State 23, Line 8
Encountered an error or an unexpected end of trace file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\Log_1827.trc'.

declare @name sysname = N'#mytabe';

with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select distinct
 pa.value as session_id
from
 (select quotename(@name)) o(name) cross apply
 sys.dm_exec_cached_plans cp cross apply
 sys.dm_exec_query_plan(cp.plan_handle) qp cross apply
 (select value from sys.dm_exec_plan_attributes(cp.plan_handle) where attribute = N'optional_spid') pa
where
 qp.query_plan.exist('//*[@Database = "[tempdb]" and @Table = sql:column("o.name")]') = 1 and
 pa.value <> 0;

with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select distinct
 pa.value as session_id
from
 (select quotename(@name)) o(name) cross apply
 sys.dm_exec_cached_plans cp cross apply
 sys.dm_exec_query_plan(cp.plan_handle) qp cross apply
 (select value from sys.dm_exec_plan_attributes(cp.plan_handle) where attribute = N'optional_spid') pa cross apply
 (select qp.query_plan.query('//*[@Database = "[tempdb]" and @Table = sql:column("o.name")][@Index]').value('(*/@Index)[1]', 'sysname')) i(name)
where
 qp.query_plan.exist('//*[@Database = "[tempdb]" and @Table = sql:column("o.name")][@Index]') = 1 and
 pa.value <> 0 and
 exists(select 1 from tempdb.sys.sysindexes where quotename(name) = i.name);

with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select distinct
 pa.value as session_id
from
 (select quotename(@name)) o(name) cross apply
 sys.dm_exec_cached_plans cp cross apply
 sys.dm_exec_query_plan(cp.plan_handle) qp cross apply
 (select value from sys.dm_exec_plan_attributes(cp.plan_handle) where attribute = N'optional_spid') pa cross apply
 (select qp.query_plan.query('//*[@Database = "[tempdb]" and @Table = sql:column("o.name")][@Index]').value('(*/@Index)[1]', 'sysname')) i(name)
where
 qp.query_plan.exist('//*[@Database = "[tempdb]" and @Table = sql:column("o.name")][@Index]') = 1 and
 pa.value <> 0 and
 exists(select 1 from tempdb.sys.indexes where quotename(name) = i.name and object_name(object_id, db_id('tempdb')) like @name + N'[_]%');

  -- Проверяем наличие врем.таб-флажка. Если таблица есть, то "на выход". Если нет - создаём.
  if exists (select 1 from tempdb..sysobjects where name like '#Flag%')	
  begin
	-- "Флажок" - есть, ХП УЖЕ выполняется, - на выход
    	RAISERROR ('!', 16, 1)
	return 0	
  end

DECLARE @result int;
EXEC @result=sp_getapplock 
	@Resource = 'test1'
	,@LockMode = 'Exclusive'
	,@LockOwner = 'Session'
	,@LockTimeout = 0;