Could not Alter/Drop the trigger

Question

I could not ALTER the trigger as its taking more then 80 min, and cannot delete/drop also , while trying to do this from Designer mode it shows erroe as

->Drop Failed for trigger. "LOCK request timeout period exceeded." But the same trigger has been ALTER/DROP in other database containing same table.

• I have changed the Table and Database Designers transaction timeout after to 30000 sec as suggested by some of the Forms.
• And Uncheck Prevent saving changes that require table re-creation inside TOOLS->OPTION->DESIGNER. After running Activity monitor

Is my trigger not ALTERING/DROPPING Due to this ? Or some other reasons?

Answer 1

Most probably there is another process with an open transaction on the table. To find what process has open transactions:

1. DBCC OPENTRAN (Database)
2. exec sp_who2
3. from SSMS right click on the server name, select Activity Monitor. If you run it while you are trying to alter the trigger, you can look in the blocked by column.

then you need to end the spid that has the open transaction or worst case to kill spid