0

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.

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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