Re: Problem with spid by sp_who

Matik (marzec@xxxxxxxxxxxx) 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@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Connection types and speeds
    ... The memory used by holding the connection open is not ... All recordset inserts seen to consequently have 0ms execution time, ... actually written to the database while the code has regained control ... RecordsetClone of a form) when a transaction was rolled back, ...
    (microsoft.public.access.queries)
  • Re: psycopg, transactions and multiple cursors
    ... > first time a change is made to the database. ... My intention is actually to keep them in the same transaction. ... >> cursor fetches one record from the database at a time). ... > generated from the same connection, ...
    (comp.lang.python)
  • Re: ADO Connection Timeout
    ... so what happens when a connection failure forces one station to revert ... to a local database? ... Further, you *will* have contention issues, Jet does not support record ... to the central server, but you are willing to live with periods where it ...
    (microsoft.public.data.ado)
  • RE: Rollback transaction
    ... Write a common routine that will create a single connection object. ... Begin and End transaction block, read each recordset and update the database. ...
    (microsoft.public.vb.database.ado)
  • Re: Driver AutoCommit issue
    ... I am using the container managed transaction and I expect the container to handle that for me. ... Is there different driver class that i have to use in the connection pool configuration. ... Why I am saying the DML always gets committed is when I step thought the code I can see the updated data in the database immediately after the callable statement is executed Even before the EJB method that invoked the call is completed. ... I set the auto commit to false on connection as soon as I get the connection from the datasoruce. ...
    (microsoft.public.sqlserver.jdbcdriver)