2

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?

Improve this question
9
  • Go here: whoisactive.com, install that stored proc, and run sp_WhoIsActive @get_locks = 1 and post the results.
    – Erik Reasonable Rates Darling
    Commented Apr 6, 2017 at 19:06
  • I ran that proc, zero results.
    – tuj
    Commented Apr 6, 2017 at 19:08
  • Interesting! Try EXEC sp_WhoIsActive @get_locks = 1, @show_sleeping_spids = 1, @show_system_spids = 1
    – Erik Reasonable Rates Darling
    Commented Apr 6, 2017 at 19:09
  • No, zero plug-ins
    – tuj
    Commented Apr 6, 2017 at 19:12
  • @sp_BlitzErik I ran with those additional parameters and everything is listed under the 'sa' login. I don't see anything under my login or my application user login.
    – tuj
    Commented Apr 6, 2017 at 19:14

2 Answers 2

Reset to default
4

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.

This does not tell wether the locks memory is exhausted or not. SQL Server will use for the lock manager either the configured sp_configure 'locks' value or, by default, some percent of the available total memory. The lock manager can well exhaust the available locks memory w/o the total memory exceeding the 14GB you see. you need to check SQL Server, Memory Manager Object:

Lock Blocks Specifies the current number of lock blocks in use on the server (refreshed periodically). A lock block represents an individual locked resource, such as a table, page, or row.

Lock Blocks Allocated Specifies the current number of allocated lock blocks. At server startup, the number of allocated lock blocks plus the number of allocated lock owner blocks depends on the SQL Server Locks configuration option. If more lock blocks are needed, the value increases.

Lock Memory (KB) Specifies the total amount of dynamic memory the server is using for locks.

Lock Owner Blocks Specifies the number of lock owner blocks currently in use on the server (refreshed periodically). A lock owner block represents the ownership of a lock on an object by an individual thread. Therefore, if three threads each have a shared (S) lock on a page, there will be three lock owner blocks.

Lock Owner Blocks Allocated Specifies the current number of allocated lock owner blocks. At server startup, the number of allocated lock owner blocks and the number of allocated lock blocks depend on the SQL Server Locks configuration option. If more lock owner blocks are needed, the value increases dynamically.

However, in your case, I do not believe the problem to be lock manager memory exhaustion. It could be a lock held by an orphaned transaction, although is very unclear from your explanation. To begin with, post the exact error message your application is seeing. Normally applications either get the lock, or they wait. An infinity. A 'could not get the lock' message would imply some very specific context (eg. attempt to obtain a lock for ALTER DB). So is very important to know what message you get, and the exact statement being executed when you get the message.

Next step, depending on the result of the previous steps, is to look into sys.dm_tran_locks.

Improve this answer
3
  • The exact statement I am executing is rather complex. I have tried modifying it with WITH (NOLOCK) hints but that didn't help. It is supposed to select and insert about 5M rows. Could this be the issue?
    – tuj
    Commented Apr 7, 2017 at 1:16
  • Here's what the statement boils down to: code with x as (select distinct col_a from tbl_a with (NOLOCK) ) , t as ( select n1.col_a as col1, n2.col_a as col2 from x n1 with (NOLOCK) inner join x n2 with (NOLOCK) on n1.col_a <> n2.col_a ) insert into tbl_a_temp select * from ycode
    – tuj
    Commented Apr 7, 2017 at 12:17
  • I ran sp_who2 and this SELECT INTO statement is the only runnable statement on the database. However if I try to kill it, I get the message "Cannot kill your own process."
    – tuj
    Commented Apr 7, 2017 at 12:28
0

OK, well I found a soution but it wasn't pretty. I restored the database from a backup, then I re-wrote my process. I noticed that the tbl_a_temp in my query above had some unusual index creation code when I went to put a primary key on it. The table designer added a line to allow table locks. I took this line out, added the PK, and now the application is working correctly.

I still have no idea what exactly caused the locking of the process or if there is a cleaner way of having handled this. Thoughts?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.