Why does sp_whoisactive show locks on databases, but not tables? 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

&lt;Database name="NOG_APP"&gt;
   &lt;Locks&gt;
      &lt;Lock request_mode="S" request_status="GRANT" request_count="1" /&gt;
   &lt;/Locks&gt;
&lt;/Database&gt;


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