This week’s ‘Dear SQL DBA’ question gets us down to the essentials: how to I tell if a transaction is hanging?

Recently, when I was checking if there are any hanging transactions in my database via “sp_who2 " procedure…

• A transaction is “AWAITING COMMAND”
• LastBatch date is more than a week ago
• ProgramName is “SQLAgent - Generic Refresher”

Is the transaction hanging?

Learn the answer in this 15 minute video, or scroll down to read a written version of the answer.

Let’s talk about sp_who2

I started out using sp_who2, also! And I was often confused by sp_who2.

sp_who2 is a built-in stored procedure in SQL Server.

• Shows a lot of sessions, even on an idle instance
• Doesn’t tell you much about what it’s doing

Here’s what an idle SQL Server looks like in sp_who2

This is my dev SQL Server instance. I have a few sessions open, but the only one which was executing anything was the one you see, where I ran sp_who2. I don’t have any agent jobs scheduled on this thing. It’s just waiting for something to happen.

It’s hard to know what to look at, because we see so much. And only 19 of the 49 sessions is on the screen, too.

Scrolling down, I can see a session similar to the one our question is about

Session 52 is a very similar case: it’s awaiting command, and it’s part of the SQL Server Agent.

I just started up my instance, so the “Last Batch” column isn’t long ago. But even if it’s more recent, can we tell if this is causing a problem? Does it have an open transaction?

We can use additional old school commands like DBCC INPUTBUFFER and DBCC OPENTRAN to find out

When I learned sp_who2, I also learned to use these two commands:

• DBCC INPUTBUFFER (SPID) – what’s the last statement from the client? It returns only NVARCHAR(4000).
• DBCC OPENTRAN – what’s the oldest open transaction in a database?

And these commands work. They’re worth knowing about, just for times when they come up. (Foreshadowing: I’ll show you a better way than all of this soon.)

DBCC INPUTBUFFER

I can plug the session id from the SQL Agent activity I saw in sp_who2 into this and get an idea of the last thing it ran.

But wow, this is inconvenient when I have more than one thing I’m interested in! And also, I still don’t know if it has an open transaction or not.

DBCC OPENTRAN

I saw in sp_who2 that session 52 was in the msdb database. I can run DBCC OPENTRAN and check what the oldest active transaction is. In this case it tells me that there’s no open transaction, so session 52 seems like it’s OK.

That was a lot of steps, and it was pretty clunky.

The problem isn’t you. The problem is sp_who2. There’s a better way!

All the commands we’ve been talking about so far are from the SQL Server 2000 days. They’re old enough to drive.

In SQL Server 2005, Microsoft gave us a better way to do these things. They gave us Dynamic Management Objects. (There are views and functions.)

Microsoft regularly improves and updates the DMV queries. They’re awesome! Commands like sp_who2 and friends are still there for backwards compatibility.

• Major pros to using Dynamic Management Objects: way more information
• Small downside: complex to write your own queries

Meet sp_WhoIsActive

That downside isn’t really a downside: Adam Machanic is a SQL Server expert, and he has a great free stored procedure that gives you all the DMV queries you need to see what’s running in your SQL Server.

Free download: whoisactive.com

Right out of the gate, sp_WhoIsActive doesn’t show you the stuff you don’t need to worry about

• Only shows you a ‘sleeper’ if it has an open transaction
• Unlike DBCC OPENTRAN, sp_WhoIsActive is instance level: you don’t have to run it per database

sp_WhoIsActive immediately shows that my idle instance has nothin’ goin’ on

Here’s what my lazy dev instance looks like in sp_WhoIsActive:

Nothing is actually running. It’s easy to see, and that’s great – because when something really is running, it makes it easy to see.

We can see sleepers if we want – even if they don’t have an open transaction

Sp_WhoIsActive has a boatload of parameters. If you want to see user processes who are sleeping and who don’t have an open transaction, use @show_sleeping_spids = 2.

If we want to see the sql_text they ran, we can click on that column. No need to run DBCC INPUTBUFFER, it’s right there.

And if we scroll to the right, we can see all sorts of things like the calling program, the status (sleeping in this case), and lots more info. It’s everything sp_who2 shows you, plus more.

Sleepers are usually OK

A sleeping session without an open transaction…

• Isn’t holding locks on a table
• Uses very little resources
• May be re-used by the application (by something like connection pooling)

What if I have hundreds or thousands of sleepers?

I have seen a few cases where something was going wrong in connection pooling, and there were hundreds and hundreds of sleeping sessions. The longer the SQL Server was on, the more there would be.

If these sessions were to become active all at once, then things could go very badly, so it’s kind of creepy to find thousands of sleepers: it’s like looking out at a field of zombies. It’s worth addressing, and usually means connection pooling isn’t configured properly on the application servers.

SQL Server does have a maximum number of connections, by the way: 32,767.

But I’m getting a little far away from the question, which was about one sleeping session.

What if my sleeper does have an open transaction?

I cover how to diagnose and treat “problem” sleepers who have open transactions in my new course, “Troubleshooting Blocking & Deadlocks for Beginners”.

Thanks to Adam Machanic for writing sp_WhoIsActive and supporting it for many years!

• If sp_WhoIsActive has helped you, tweet to @AdamMachanic and let him know
• Follow his blog at: http://sqlblog.com/blogs/adam_machanic