Why does sp_whoisactive show locks on databases, but not tables?

Question

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.

Answer 1

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....