Drop Table
Support Forum for database administrators and web based access to important newsgroups related to databases
Problem with spid by sp_whoHello 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 log inname hostname blk dbname cmd 266 0 sleeping remote_user_name hos t_ERP 0 PRIMARY_PROCESS_DB A WAITING COMMAND 267 0 sleeping remote_user_name hos t_ERP 0 PRIMARY_PROCESS_DB A WAITING COMMAND 268 0 sleeping remote_user_name hos t_ERP 0 PRIMARY_PROCESS_DB A WAITING COMMAND 269 0 sleeping remote_user_name hos t_ERP 0 PRIMARY_PROCESS_DB A WAITING COMMAND 270 0 sleeping remote_user_name hos t_ERP 0 PRIMARY_PROCESS_DB A WAITING COMMAND 271 0 sleeping remote_user_name hos t_ERP 0 PRIMARY_PROCESS_DB A WAITING 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 (190214 1b21c7) X GRANT 53 2 1 3 KEY (e60041 ad2c6f) X GRANT 53 2 3 2 KEY (5b0233 670fb9) X GRANT 53 2 3 2 KEY (6e0212 a7298c) X GRANT 53 2 3 2 KEY (97013e ce81c8) X GRANT 53 2 3 1 KEY (e7006f 987fb0) X GRANT 53 2 1 3 KEY (ad00a3 3f46b9) X GRANT 53 2 1064356070 0 TA B Sch-M GRANT any idee? Thanks Mateusz
Post Follow-up to this message
Re: Problem with spid by sp_whoMatik (marzec@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, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pr...oads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodin...ions/books.mspx
Post Follow-up to this message
Sponsored Links
Show a Printable Version
Email This Page to Someone!
Receive updates to this thread
