I'm somewhat familiar with blocking processes and locks in SQL Server, however this one has me pretty confused. I understand this error can come from two issues: 1) if SQL Server cannot allocate more memory for locks or 2) if there is a blocking process.
I do not believe #1 is the issue as the machine has 48GB memory and only ~14GB is used. SQL Server is configured to use a max of 2,147,483,647MB of memory.
With #2, when I run sp_lock, I see a row with an ObjID and a TABLE lock, mode IS. This lock is under the same process I am running SSMS under. Therefore when I try to do a KILL on the process, I get the error "Cannot use KILL to kill your own process."
I have restarted the server a couple of times and closed SSMS and still this lock persists. My program can't do work on the relevant table because of the lock; it keeps getting the error message that SQL Server cannot obtain a lock and to try again at a less-busy time. There is no activity on the server, I am the sole user. Therefore I do not believe this is a resource issue, but firmly related to the lock I can't seem to get rid of.
I have run a modified sp_lock procedure that uses sp_who in conjunction to try and determine who is blocking who. In this view, I see my userid has one IS mode lock, but it is not an exclusive lock.
How can I release the table lock? What is happening with the database? How can I avoid this issue in the future?