Problem with spid by sp_who

[Matik]

Hello 2 all,

Maybe my question can be very stupid, but I'm a little confused.
When I run sp_who on my database, I see one process (accessing remotely
my database from another database on another SQL server) many many
times.

Well, I assume that this is one process, because I cannot imagin that
the data aquiring can be done by almost 1000 (thousand) opened
connections.
This is what I have. Almost 1000 spids running (sleeping) with cmd =
awaiting command.

The best part is, that when I run sp_lock, I see, that the current
session of the client, (lets say spid 53) is locking tempdb, with
objects id below 10 (system).

As far as i know, and as far as I'm using this in my software, there is
no necessity to open-close-open-close connection.
Normaly, you can just open a connection to db, with assigned spid, and
operate within this just one. After the disconect ist done, the session
shoould be removed.

I think, there is something wrong in the way, the remote system is
connecting to my database, but i'm not sure is it normal what I'm
expieriencing or not.

below, a piece of that what sp_who returned:

SPID ECID status loginname hostname blk dbname cmd
266 0 sleeping remote_user_name host_ERP 0
PRIMARY_PROCESS_DB AWAITING COMMAND
267 0 sleeping remote_user_name host_ERP 0
PRIMARY_PROCESS_DB AWAITING COMMAND
268 0 sleeping remote_user_name host_ERP 0
PRIMARY_PROCESS_DB AWAITING COMMAND
269 0 sleeping remote_user_name host_ERP 0
PRIMARY_PROCESS_DB AWAITING COMMAND
270 0 sleeping remote_user_name host_ERP 0
PRIMARY_PROCESS_DB AWAITING COMMAND
271 0 sleeping remote_user_name host_ERP 0
PRIMARY_PROCESS_DB AWAITING COMMAND

and these are locks from sp_lock 53 which is the current running
process:
53 7 0 0 DB S GRANT
53 2 6 0 TAB IX GRANT
53 2 1 0 TAB IX GRANT
53 2 3 0 TAB IX GRANT
53 2 2 0 TAB IX GRANT
53 2 12 0 TAB IX GRANT
53 2 9 0 TAB IX GRANT
53 2 11 0 TAB IX GRANT

53 2 3 2 KEY (1902141b21c7) X GRANT
53 2 1 3 KEY (e60041ad2c6f) X GRANT
53 2 3 2 KEY (5b0233670fb9) X
GRANT
53 2 3 2 KEY (6e0212a7298c) X GRANT
53 2 3 2 KEY (97013ece81c8) X GRANT
53 2 3 1 KEY (e7006f987fb0) X GRANT
53 2 1 3 KEY (ad00a33f46b9) X GRANT
53 2 1064356070 0 TAB Sch-M GRANT

any idee?

Thanks

Mateusz

[Erland Sommarskog]

Matik (ma****@sauron.xo.pl) writes:

Maybe my question can be very stupid, but I'm a little confused.
When I run sp_who on my database, I see one process (accessing remotely
my database from another database on another SQL server) many many
times.

Well, I assume that this is one process, because I cannot imagin that
the data aquiring can be done by almost 1000 (thousand) opened
connections.
Unfortunately, I can imagine that. :-)

Modern applications, usually opens a connection to run a query and then
disconnects. The idea is that the API then maintains a connection pool.
That is, when the application says "close", the API lingers to the
connection for some time, typically 60 seconds, and if the application
requests a new connection with the same properties within that time,
the API will reuse the dormant connection.

But if the application is improperly written, the connection cannot be
reused. I know that there is a pitfall with SqlClient in ADO .Net. I
don't remember the exact details, but it has something to do if you
close the connection before you close the command or somesuch.
The best part is, that when I run sp_lock, I see, that the current
session of the client, (lets say spid 53) is locking tempdb, with
objects id below 10 (system).

It looks as the processes have been creating objects in tempdb within
a transaction. And since the syscomments is among the listed tables,
one could suspect that it has created stored procedures. (But it could
also be default values for table columns.)

Note that if a process starts a transaction and then disconnects, but
the API retains the connection open, the transaction will remain open
until the connection is reused. (When the connection is reused, the API
issues sp_reset_connection which rollbacks transaction, reset SET options
etc.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx