Sql-server – Why does sp_whoisactive show locks on databases, but not tables

sp-whoisactivesql server

Recently I have noticed some threads running on our sql server which are invoked from our application, which are in a sleeping status for a few minutes and have open transactions (usually just one).

I added the parameter @get_locks = 1 to sp_whoisactive to see what they were holding and the xml result set looks like this

<Database name="NOG_APP">
   <Locks>
      <Lock request_mode="S" request_status="GRANT" request_count="1" />
   </Locks>
</Database>

I don't see any objects in here… how am I to interpret this? Does it mean that this thread is not holding any locks? If so why doesn't the output return NULL?

Running SQL Server 2012 SP3.

Best Answer

It means someone has a connection open in that database. You can reproduce it by:

USE NOG_APP;
GO
BEGIN TRAN;
GO

And then checking sessions from another window:

sp_WhoIsActive @get_locks = 1;

If you think that's your biggest problem, though, it's probably time to check in with users and ask them about their biggest database complaints. I think you're probably barking up the wrong tree. I can only wish my biggest problem would be open sleeping transactions with nothing locked....

Related Question