October 14, 2014 at 7:43 am
I'm not sure if I have a problem or not. I'm running an Access application against SQL Server 2008 R2, and have Idera's SQL Check continuously operating on a spare machine on my desk. It's several times alerted me to something that got stuck, and lets me keep an eye on how the machine is loaded. Today I noticed that there is only one user running this app, but SQL Check shows several processes seemingly waiting for something. The status line is sleeping, the cmd line is AWAITING COMMAND and the inputbuffer line is IF @@TRANCOUNT > 0 COMMIT TRAN
None of them are doing anything, and the user had open what should be a single read-only query. Does anyone know what these 'open' processes might be? Could it be sloppy coding, from recordsets opened but not closed? Does SQL Server 'cache' connections in case they are needed again quickly, even after the user abandons them? It shouldn't be a timing artefact - I have SQL Check set to refresh every two seconds. The number of these process bubbbles varies from two or three to almost ten. It is definitely from that one app, since they all vanish immediately when the app is entirely closed. Also, the SQL Check displays the user name, and they were all from that one user.
October 14, 2014 at 8:17 am
Sleeping is a connection that's not doing anything. It's not waiting for anything (other than more work to do), it's not taking up CPU, it's not doing any work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2014 at 8:24 am
Your application is opening a connection and never closing it, that could be intentional, they expect they'll use the connection again at some point, or just sloppy coding, they're not bothering to do cleanup when they're done with the connection.
October 14, 2014 at 8:29 am
ZZartin (10/14/2014)
Your application is opening a connection and never closing it, that could be intentional, they expect they'll use the connection again at some point, or just sloppy coding, they're not bothering to do cleanup when they're done with the connection.
Okay, thanks. I'll take a look at the app code - probably an Access recordset opened and not closed properly.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply