CODEWITHSUNDEEP
sqlsql-servertriggers
Herb Caudill
Herb Caudill

Reputation: 49952

Disable trigger hangs?

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.

Upvotes: 2

Views: 4709

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294287

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.

Upvotes: 5

Related Questions