Sql server timeout problems

[deepsheep]

I have a database that has timeout problems. I'm running SQL server 2000 on windows 2000 advanced server. I have multiple databases on one server, but seem to only have problems with one database. The problem occurs every few weeks and has started to become annoying.
Symptoms:
1 ) A VB6 program running at night, processing data, will timeout.
2 ) When I try to look at the data in Enterprise manager, I can view data that was inserted before some time in the past day. So I can view old data and I can't view new data. When I try to look at new data, I get a timeout error.
3 ) Query analyzer takes too long a time to return the results of a Select statement to view new data. 5+ minutes for 40-50 records.
4) Web pages and other programs trying to access the new data will timeout.

Fixes (usually only need to do one of the two) :
1) Restart the SQL server machine. Restarting the just the SQl server hangs the machine.
2) Close a VB6 program running on another machine that maintains a connection to the database, wait for a couple of minutes and restart the program.

After I 'Fix' the problem, everything works normally. Selects are fast, I can view all data and processing can compleate.

None of my other databases seem to have similar problems even though they do almost the exact same work and have similar programs with open connections. My processor usage is no higher when this happens than normally.

I would like to have some way to fix this more permamently and a good explanation of what is causing the problem.

Thanks for any ideas!

 

[mrdenny]

Sounds like that VB 6 program running on the other machine might be keeping a transaction open with it's connection. Have you looked to see if there any any transactions being blocked, or any locks on the database.

It sounds to be that your queries from your nightly job are being blocked by another transaction.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[timscronin]

You might try running profiler at night watching this connection. There might be a slight impact watching that connection but you should be able to watch what the issue migh be (blocking,locking, etc)

 

[deepsheep]

I'm still looking and haven't found anything unusual.
What about one of these KB articles?

http://support.microsoft.com/default.aspx?scid=kb;en-us;815056

http://support.microsoft.com/default.aspx?scid=kb;en-us;305635

Thanks.

 

[mrdenny]

For the first one you will receive 17883 in the error log.

Are you seeing anything in the error log?

The second one looks like you should only really see it when the database is expanding. Is this happening to you?

Denny

--Anything is possible. All it takes is a little research. (Me)