SQL Server Connection Refresh

[skc]

Running a web application on IIS Server 7.0, windows 2008 R2 (64Bit) and sql
server 2008 (64bit). The application looses connection to sql server
inconsistently.

In the event viewer following error is shown for SQL server:

The client was unable to reuse a session with SPID 55, which had been reset
for connection pooling. The failure ID is 29. This error may have been caused
by an earlier operation failing. Check the error logs for failed operations
immediately before this error message.

Any suggestions/ideas would be greatly appreciated.

Thanks,

--
skc

[Erland Sommarskog]

skc (s...@discussions.microsoft.com) writes:
> Running a web application on IIS Server 7.0, windows 2008 R2 (64Bit) and
> sql server 2008 (64bit). The application looses connection to sql server
> inconsistently.
>
> In the event viewer following error is shown for SQL server:
>
> The client was unable to reuse a session with SPID 55, which had been
> reset for connection pooling. The failure ID is 29. This error may have
> been caused by an earlier operation failing. Check the error logs for
> failed operations immediately before this error message.

Searching to Google, lead me to http://support.microsoft.com/kb/937745.
But the fix is for SQL 2005, and is two years old. Nevertheless the
article gives some insight.

I also found
http://social.msdn.microsoft.com/Forums/en-SG/sqldatabaseengine/thread/af79c38f-3996-49f6-b5a8-fe10e29b13f8
which also gives some information about the underlying problem. Although
there is no direct resolution, it is possible it may give some ideas
how you should work around the problem.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Sylvain Lafontaine]

Check your web application code for any unclosed transaction (uncommitted or
not rolled back). If you open a transaction, you should always finish it
(with either a commit or a rollback and close the connection); even if you
are making only reading Selects in this transaction; otherwise the
transaction is left open, the locks are hold and the connection must be
marked as Reset so that the associated transaction will be closed and the
locks released but only later, when the connection will be reused from the
connection pool. If there are to many of them, you can see this error.

The same thing might happen if you open a recordset but you don't fully read
it and you don't close it. Always close explicitely any opened recordset;
especially if you don't fully read them.

Another possibility would be a time-out ocurring for the ASP or ASP.NET page
because the code take to long; as this will also leave open any transaction
and locks and will require the connection to be marked as to be Reset. If
time-outs happen frequently, change your code for more performance, increase
the time-out period or use the XACT_ABORT option ON. You could also
deactivate the connection pooling.

See http://support.microsoft.com/kb/224453 for more details.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

"skc" <s...@discussions.microsoft.com> wrote in message
news:61B862C4-77DF-46B0...@microsoft.com...

[Edward C. Otto III, Esq.]

I would think that it's not a real good practice to use transactions if you
are only READING data and not updating it....

"Sylvain Lafontaine" <sylvainlaf...@yahoo.ca> wrote in message
news:eRIRR9K7...@TK2MSFTNGP04.phx.gbl...

[Sylvain Lafontaine]

"Edward C. Otto III, Esq." <edwar...@comcast.net> wrote in message
news:087C3AC3-86C7-47CB...@microsoft.com...

>I would think that it's not a real good practice to use transactions if you
>are only READING data and not updating it....

This is a very surprising statement and honestly, the use of transactions is
not limited to the cases where you want (or have the possibility) to write
to the database. Transactions are also about consistency of data over some
period of time; for example when you have to build a report based on some
active data - data that could change between the beginning and the finishing
of the report - but still want it consistent.

Probably that I could get other examples but suffice to say that when you
read litterature about transactions and locks, the possibility that these
should be reserved to writings and updates is never mentionned.