I never post my problems but I have no time right now

I just released a beta and I am too busy with the testers right now.

New company. Brought into automate the back office and to advise on the design on their software. Lots of sloppiness here. Denormalized tables, inline SQL all of the usual crap I get paid to cleanup.

I have'nt gotten to this part of there process yet but they do some over night processing that can take up to 12 hours. (I can't wait to look at this code).

Data entry people get here at 6 am and their applications can't seem to connect to the SQL Server but the processing has stopped. They just hang and hang. The network guy comes in about 8 am and restarts the SQL Service Fonzi style and everything is fine. The DBA thinks it's a network issue which seems a tid bit ridiculous to me since restarting the service "fixes" it.

I suspecting it's a memory issue.

Any ideas on where to start.since you're one of the good guys i will ask you if you have a preference for what to rename the subject line of your question to, since the current subject is too nebulous|||on the other hand, it is fairly distinctive so i may just leave it

:) :) :)|||rename constipated SQL Server|||Log on to the SQL server, and see what is going on in Task Manager, first. You have probably already done that, but it does sound like a resource problem somewhere (memory, CPU, etc.). Can we hope this is SQL 2000? Or are you stuck with 6.5?

If there are no resource problems, and the operating system is running smoothly, you probably have some sort of open transaction hanging everyone up. The data entry application may have commandtimeout set to "0", which sounds about in line with what you describe of their code.|||My bet is on SQL object locking. I'd check sp_who (definitely not sp_who2 until I knew it was safe) before rebooting to see if a spid is blocking the rest of the known universe.

The lovely little nightly process might be camping on some resource that everybody needs to "wake up the daemons" in the morning. When you kick the ground out from under it (by restarting SQL), the locks are gone and life is lovely once again.

-PatP|||Yes it is SQL 2K.

Thanks guys. I will let you know the resolution. Of course I will have to come in at 6 am. No VPN here. Small shop.

Have a good weekend.|||How long does the "fix" take after it's restarted?

Are the files set to autogrow?

Is the log being blown out?

How do you know if the 12 hour batch windows actually completes.

Is there enough space left on the drives?

Are there any other services on the box that are in competition for the memory?

If there are, my first reccomendation would be to isolate|||Thanks. I will know more on Monday. I just have'nt had much time to think about this due to the revamp project from hell. I plan on being here early to gauge what is going on.

===========================================
How long does the "fix" take after it's restarted?

Immediate

Are the files set to autogrow?

Yes

Is the log being blown out?

Not sure I would have to check the log size before and after.

How do you know if the 12 hour batch windows actually completes.

They tell me so when I ask.

Is there enough space left on the drives?

Yes

Are there any other services on the box that are in competition for the memory?

This box is dedicated the SQL Server.

If there are, my first reccomendation would be to isolate|||My bet: uncommitted transactions in the nightly process.