Performance question - Restarting the SQL server resolved my timeout problem

[serge]

We have an ASP application installed on one powerful Win 2003 and
the SQL Server on another Win 2003.

We have two copies of the application (identical, one for production
and one for test purposes) that are operational. The 2nd application
is connected to the same SQL Server but a test copy of the production
database.

Today, running the application, a place where it shows a list of records,
the ASP application was timing out after 30 seconds for a lot of users.

I backed up the production db and restored it over the test db and tested
the ASP application; within 10-15 seconds the ASP page loaded the list
of the records, so i didn't get any timeout. So i started to think that
maybe
the test db when it was being restored, SQL Server would do some kind of
data cleanup and defragment the new db.

But, then i checked the Task Manager, SQL Server was almost using 1.2GB
of memory and the server had only 100megs available.

As soon as i restarted the SQL Server, the ASP page did no longer timeout
on the production database.

So now i am trying to figure out why the SQL Server restart fixed my
problem?

Unfortunately i did not check if there were a lot of connections in the
Process info
under the Current Activity in SQL EM.

Do you think there were a lot of sessions that weren't killed by the
application?
And that was causing the timeout? But why only for the Production db and not
also
for the test db?

I mean, i am puzzled as to why before restarting the SQL Server, running the
ASP
application connected to the TEST database did not time out!

As always, I appreciate any feedback, comments.

Thank you

[Spieg]

I've the same problem.

"serge" <se****@nospam.ehmail.com> ha scritto nel messaggio
news:x7*********************@news20.bellglobal.com ...

We have an ASP application installed on one powerful Win 2003 and
the SQL Server on another Win 2003.

As always, I appreciate any feedback, comments.

Thank you

On one machine, sometimes my VB applications get timed out.
The only solution to avoid this is to shut down all the applications running
on the DB,
Stopping the SQL Server service and then restarting it.
After this operation, the applications start to run fine.
I'm sure I've no connection or server side recordset open that could use for
memory leak since I've tested software monitoring every operations and do
not leave any recordset or connection open.
I always destroy them, even in case of system or application errors, there
is always the on error statement calling for the recordset and connection
destruction (the set cn (or rs) = Nothing).
I do not know why, but I suppose is due to ...... lack of memory on the
server (I've to use a 256 MB computer as SQLServer).
Any idea would be very usefull also for me.