I've got an odd problem that's cropped up twice now. I know how to fix it but not how to prevent it from happening again. We've got a .Net Webform using VB.Net 2003 that calls a stored procedure called usp_sp1 (names have been changed to protect the innocent). The stored procedure is in a SQL 2000 database on another server in the same room. When I call this stored procedure, it takes longer than 30 seconds and times out. I've got another web page that lets administrators run SQL code directly. When I type in usp_sp1 and run it in this other page, it times out after 30 seconds. When I open the stored procedure, copy all of the code, and run it in this other admin web page, the results come back in 5 seconds.
When I open Query Analyzer with the same SQL authenticated account that the web pages use, and call usp_sp1, the results come back in 5 seconds. At this point, we were pretty lost and could not figure out where the problem was coming from. DBAs pointed to .Net, .Net developers pointed to the database.
We created a new stored procedure called usp_sp2 and pasted in the contents from usp_sp1. When we call that stored procedure from the admin webform, it comes back in 5 seconds. In Query Analyzer it comes back in 5 seconds.
We renamed usp_sp1 to usp_sp1_bad, opened it and edited it's name within as well. We then renamed usp_sp2 to usp_sp1, opened it and edited it's name within. Now when we call the original webform, the results come back in 5 seconds. The ASP worker process was NOT restarted, the web server was NOT rebooted, essentially, nothing was changed on the web server.
We then decided to try and figure out what was wrong with usp_sp1_bad. We called it from the admin page and it came back in 5 seconds. Huh? When it was called usp_sp1 it timed out after 30 but now that it's called usp_sp1_bad, it comes back in 5 seconds. When we call the new usp_sp1 from the original webform, from the admin page, or from Query Analyzer, it also comes back in 5 seconds.
Basically, at this point, we're thinking that had we renamed usp_sp1 to usp_sp2 then renamed it back to usp_sp1 it probably would have fixed it as well.
The question though, is not how to fix it. The question is why the heck is this happening and how do we prevent it from happening again? This same stored procedure has done this twice in the last two months. Both times we fixed it the same way. This time we let it stay broken for at least a half an hour if not longer to try and troubleshoot what the heck is causing it and got nowhere. Nothing out of the ordinary is going on at the web server or database server when the problem occurs.
Anyone have any ideas or ever seen this happen before?
Thanks,
Check
