Stored Procedure Times Out from Web Application

[rokslide]

Hi all,

I have a web application which (until a few weeks ago) was running fine.

Now, periodically, a stored procedure will stop responding and timeout when called via the web application. The stored procedure varies (eg. so far this problem has happened twice and it has been a different stored proc each time) and if the stored procedure is executed in the query analyzer it executes without a problem and within a second.

Looking at what is happening via SQL Profiler I can see the Stored Proc starting but it never completes (atleast not with in the timeout period).

Last time this happened we spent a number of hours trying to locate the problem but eventually it just vanished.

Looking at the activity logs and sp_who etc we can see that there are no locks on the database. Looking at Task Manager there is no load on the web server or on the database server. Memory usage is fine (eg. there is plenty to spare).

If anyone has any idea what could be happening I'll love your input.

Cheers,
Shaun

[scripto]

one possibility could be if the web application is starting a process but never quite completing it - so now Sql server has to wait until the app is finished processing the previous call before continuing...th e problem may be in the web application and not with your stored procs.

[rokslide]

It's possible that there is a networking issue somewhere, but I don't think that is likely.

Reading through other threads of people with similar problems I think it might be a space allocation issue. Someone else was having a similar problem, but if they ran the stored proc in the query analyzer using the credentials of the web application user everything would start working again. I tried this and while it didn't start working immediately it did shortly afterwards. In a reply to their thread someone said that this was likely to be a space allocation issue. The only thing I don't get about this is why it would matter who you were logged on as....