Hangs on Inserts

[benvegiard]

Hi All..

I have a SQL2K (sp3a) box running on Win2k and authenticating against a W2k domain controller.

The data began exhibiting a symptom of not allowing inserting of data at random times. It will read data just fine (and with no visible loss of performace) We wrote a program that inserts a dattime into a table every 10 minutes to track when the "hanging" of inserts was happening. For the first several days we found that it would hang around 4:20p through 11:00p. Then it would accepts inserts for about 1 1/2 hours, then go back to hanging between midnight and 12:30a and be down until we restarted the system in the morning. After stopping and restaring the service, it would usually work just fine during the work day. Occasionally it will hang during working hours.

The performance monitor show Cache Hits going nuts during these insert hangs. EM show no unusual activity on the active processes.

The "network boyz" have tried tuning the physical and virtual memory, and other variables, but to no avail.

At one point we noticed that a client application had erronious login into the server. We cleared that problem and the downtime moved from 4:20 to 4:50, but the rest of the pattern remained the same.

Yesterday I checked the index stats and noticed they were way out of date (even though Auto Update Stats is active) so I forced an update of the stats database wide. Last night the server remained available for insert until midnight, then went away until 6:20a. It has been up since 6:20a (and we haven't touched it since yesterday, other than for normal client application activity). I like the change in the pattern (no hang from 4:40p - 11:00p ish) but still can't determine what is causing the midnight to early morning hang.

ANY THOUGHTS? ANY AT ALL?

Thanks,
Ben

 

[SQLSister]

Sounds to me like you had one or more jobs running at night which might have been timing out or blocking other jobs. Use SQL Profiler to see what is happening at night. With this you can see exactly what queries are being sent and processed.

Also you don't mention having checked the SQL server/network error logs. Perhaps they can shine some light?

 

[benvegiard]

Sorry not to mention those details. We have looked through EM and are not finding any corrilation with scheduled jobs or Management tasks.

The Event Logs (SQL & 2K) are not showing us anything unusual.

Ben

 

[SQLSister]

Is some outside application running a job from it's end at that time? You'll need to use profiler to find out exactly what is calling onthe SQL server during the time in question.