Let's say I did something really stupid by creating a trigger that has an infinite loop in it. It's not nesting, or recursive; I just have a WHILE loop in it that apparently isn't exiting.
Now I can't access the trigger at all... I can't update it, can't drop it, can't disable it. And none of my programs that use the table it's attached to can do anything with the table because of it. I've exited all the way out of SQL Server, but when I get back in, I have the same issue.
Any way to kill that trigger?
Provided that you have the requisite permissions here is what you can do:
Open / Run SSMS and connect to the server in question. Change to use the correct database
in SSMS, run SP_Who2
This gives you a list of all connections, and their state.
Take any one connection SPID# and run DBCC INPUTBUFFER (SPID#)
If that connection is running your trigger, you can kill the connection by issuing KILL SPID#
Then read up on why to avoid triggers.