Disable trigger hangs?

Question

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.

Answer 1

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.