We have recently migrated from a Win 2003/SQL Server 2000 system to Win 2008 64 bit R2, SQL Server 2008 R2.
Our websites are in classic asp, and this can't be changed to another scripting language at this time.
On the old server, if I got stuck in some kind of endless loop, the page would throw an error.
On the new server, I have a page that has some sort of looping problem, that even though the SQL SP is called only once (and runs fine run as a query on the server) it pegs SQL server and therefore locks all of our websites.
I'll get my code figured out, no biggie. But I need to make sure the server times out when this happens. (The page I'm working on runs fine with certain instances of the query, and locks with others using a different query variable. I can't have something like that sneak up on me on a page I haven't touched for three years.)
I can't figure out how an SP that runs once on the server, from an ASP page, is tying up SQL server this way. It's obviously some sort of a timeout issue, but I can't figure out where/which timeout values to change.
I actually have to remote desktop to the server and kill the process in SQL server.
I'm afraid I'm a generalist, and server management is not my thing, even though it's my responsibility, so I am almost certain to have questions about any answer that I receive.
How can I track this down? What settings do I need to change?
More info: It's not SQL Server
On our test site, I created an ASP file that just did an endless loop (do while 1=1) and had the same problem - the other websites wouldn't load - without SQL server being involved. So I think the reason the process was hanging is that the page wasn't timing out as it should, and so the connection to SQL was never closed. Killing the process in SQL server would reset the page somehow.
For my intentional endless loop, I had to refresh the app pool to get rid of it. This points more to either IIS or the ASP settings.
The ASP timeouts are set to whatever the default were when the server was first loaded.
I still can't figure out why one file is locking up all websites, though. Again, that didn't happen on the old server.
