We have a production CLR process that queries Active Directory for User Information and loads it into a table. We had 2 jobs that collided and ended up with blocking and hung jobs accessing the table. In an attempt to stop and restart the process I killed the job, however the process would not rollback as I believe the CLR thread still had a lock on the table. The process was in a rollback state and never moving(this is a small transaction with no more than 100 or so rows)
I know this is not an ideal situation, but was curious for opinions on how to get rid of the lock on the table:
My understanding is you can use the kpid from the sysprocesses table and map to a process on the server, and kill it there, but you run a likely risk of crashing SQL Server completely.
I was thinking the most likely way to clear the process is to restart the SQL Service, however this would have been a large impact to the other databases on the instance.
My thought and what I was looking for feedback on was switching the clr enabled function from 1 to 0 and then back to 1. My thought was this would unload the assemblies and kill the process. There was minimal impact as we only have a few databases using CLR assemblies compared to restarting the entire service.
Thanks for the feedback