About me

Fellow SQL Server enthusiast and online friend of mine, Jonathan Kehayias is very active in the MSDN Forums. He frequently provides good, insightful information to those seeking advice.

In response to a recent question in the Database Engine Forum, Jonathan provided a good example how a process marked as sleeping can still hold locks on a SQL Server resource. I'd like to share a slightly modified version of it with you; you can read his original postings here.

Let's consider a very simple table in tempdb. Use SQL Server Management Studio to execute the following query:

--limit the results to only the demo table
SELECT
*
FROM
sys.dm_tran_locks
WHERE
resource_database_id = @dbid AND
resource_associated_entity_id = @objectid ;

The first resultset shows that there are four locks in the tempdb database. Obviously, I'm creating this demonstration on a non-production server. The second resultset limits the output on only the demo table.

And now, let's look at the information for the offending process.

--examine the
EXEC sp_who 58 ;

From this resultset, you can see that our process is considered 'sleeping' and 'awaiting command', yet it maintains its locks on the table.

When you give this some thought it makes sense. The process isn't actively doing anything at the moment, however it has an active transaction in progress. Until that transaction is either committed or rolled back, the locks will remain.

Go back to the original query window and commit the transaction.

--commit the transaction
COMMIT TRANSACTION ;

Re-running the queries from the second query window shows that the locks have been released.

This super video converter for mac is developed by Emicsoft Studio, it is currently the best video converter running under Mac os x, comparied by isqunite, Visualhub and other Video Converter for Mac Free vide under "format" option choose the output video formats

thanks! very love it "We already know about sleeping locks. Does anyone have any thoughts on releasing those locks from outside of the process holding them? Of course, doing this without killing the process. ....” very amazing, I love it, thanks!