1

I need to do this from an ASP.NET web app:

Alter Table Contacts Disable Trigger All 
-- Do some stuff
Alter Table Contacts Enable Trigger All

In some situations the Disable Trigger statement hangs. Where should I start looking to figure out what's causing this? If I restart SQL server it goes back to behaving normally.

Herb Caudill
  • 50,043
  • 39
  • 124
  • 173

1 Answers1

4

Look into the Activity Monitor from SSMS to see why it blocks. Or you can look into blocking_session_id column is sys.dm_exec_requests.

My guess: schema changes require a schema modification lock on the table. Any operation (like SELECT, UPDATE etc) will place a schema stability lock on the table, blocking any ALTER until the SELECT completes. So the Disable Trigger ALTER is blcoked by all the pending table access (SELECT) statement.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Thanks for pointing me in the right direction. I used SQL Profiler to see what was going on and you were absolutely right. FWIW: The code above was inside a loop through a LINQ to SQL IEnumerable. If I execute the query first and get the results (with .ToList) and then loop through the list, everything is fine. – Herb Caudill Jan 19 '10 at 21:14