ASPState

[Janos Horanszky]

Hi Gurus!

Error: Unable to connect to SQL Server.

I am having difficulties identifying a problem regarding the ASPState
database that is bieng used by 2 applications (cms based). (two node
SQL2KESP3a cluster on Win2003E)
There is a 30 minute timeout specified for the session_ids and we are having
~18000 rows in the tables.
We have captured with profiler that an update (within an sp) gets called
~1400 times in a minute for the SAME session_id! Locks, after locks,
deadlocks, and it causes timeouts on the app server and error page to the
user.
Checking the IIS logs we didn't see anything unusual.
The table has a clustered index, that is fine, it is heavier for updates but
still shouldn't present this type of problem. Though the table (and the
index) is very fragmented. 9000 pages for 18000 rows! Tons of extent
switches. How could I prevent this?

Microsoft articles don't seem to help.
http://msdn.microsoft.com/netframewo...g/default.aspx

Anybody has encountered this problem? I assume this is a fairly common
problem. It affects our business big time and I am not sure what can I do
from SQL Server side. When the cluster fails over we loose all sessions. Why
SQL Server can not hand out more connections? Both applications are
configured to use a pool of connections but we have more than the sum of the
two from the same apps.

I'd appreciate any suggestion that would alleviate this issue.
Janos

[sara]

Hi Janos
Did u give the permisssion to asp to execute sqlserver??
"Janos Horanszky" wrote:

Hi Gurus!

Error: Unable to connect to SQL Server.

I am having difficulties identifying a problem regarding the ASPState
database that is bieng used by 2 applications (cms based). (two node
SQL2KESP3a cluster on Win2003E)
There is a 30 minute timeout specified for the session_ids and we are having
~18000 rows in the tables.
We have captured with profiler that an update (within an sp) gets called
~1400 times in a minute for the SAME session_id! Locks, after locks,
deadlocks, and it causes timeouts on the app server and error page to the
user.
Checking the IIS logs we didn't see anything unusual.
The table has a clustered index, that is fine, it is heavier for updates but
still shouldn't present this type of problem. Though the table (and the
index) is very fragmented. 9000 pages for 18000 rows! Tons of extent
switches. How could I prevent this?

Microsoft articles don't seem to help.
http://msdn.microsoft.com/netframewo...g/default.aspx

Anybody has encountered this problem? I assume this is a fairly common
problem. It affects our business big time and I am not sure what can I do
from SQL Server side. When the cluster fails over we loose all sessions. Why
SQL Server can not hand out more connections? Both applications are
configured to use a pool of connections but we have more than the sum of the
two from the same apps.

I'd appreciate any suggestion that would alleviate this issue.
Janos

[Janos Horanszky]

Hi Sara, it is not a permission problem.
On the other hand sqlserver has it words too. :)
"sara" <sa**@discussions.microsoft.com> wrote in message
news:89**********************************@microsof t.com...

Hi Janos
Did u give the permisssion to asp to execute sqlserver??
"Janos Horanszky" wrote:

Hi Gurus!

Error: Unable to connect to SQL Server.

I am having difficulties identifying a problem regarding the ASPState
database that is bieng used by 2 applications (cms based). (two node
SQL2KESP3a cluster on Win2003E)
There is a 30 minute timeout specified for the session_ids and we are
having
~18000 rows in the tables.
We have captured with profiler that an update (within an sp) gets called
~1400 times in a minute for the SAME session_id! Locks, after locks,
deadlocks, and it causes timeouts on the app server and error page to the
user.
Checking the IIS logs we didn't see anything unusual.
The table has a clustered index, that is fine, it is heavier for updates
but
still shouldn't present this type of problem. Though the table (and the
index) is very fragmented. 9000 pages for 18000 rows! Tons of extent
switches. How could I prevent this?

Microsoft articles don't seem to help.
http://msdn.microsoft.com/netframewo...g/default.aspx

Anybody has encountered this problem? I assume this is a fairly common
problem. It affects our business big time and I am not sure what can I do
from SQL Server side. When the cluster fails over we loose all sessions.
Why
SQL Server can not hand out more connections? Both applications are
configured to use a pool of connections but we have more than the sum of
the
two from the same apps.

I'd appreciate any suggestion that would alleviate this issue.
Janos