Mike,

>I've seen similar issues with AutoGrowth on the data and log files. When SQL Server needs to allocate more space, it stops all activity in the database until its completed. If you have your AutoGrow numbers at the default of 10%, back those down to something in the MB range.


That sounds like a likely possibility. It would also explain why it seems to have gotten worse as the database has grown. It's currently at 25GB and set to the default 10% autogrow.

I'm a little concerned about setting it to a low number since there are users who may append 100K+ records at a time. Seems it would be better to run some process every night that checks the available space in the database and log file and grows it manually. I seem to remember reading something about such a strategy in one of my SQL books.

BTW, I don't remember seeing any autogrow records in the log. Is there a way to customize what processes gets logged?

Thanks for the help

Dan

>-Mike
>
>>Hello,
>>
>>I have SQL Server 2000 running on NT4. Periodically -- sometimes several times a day -- users can't connect to the server for maybe 30 minutes at a time. Nor can I connect through Enterprise Manager or Profiler from the server during that time. And when SQL Server is locked up, I can hear the HDD grinding away. The task manager shows low and consistent CPU usage.
>>
>>At other times performance seems just fine for the 10-15 concurrent users doing fairly basic, light stuff.
>>
>>The server is a Proliant 3000 with 1/2GB of RAM. Nothing else is running on the server except Agent, for which nothing is scheduled during these periods.
>>
>>I can't find anything in the logs which indicate any activity during these periods. Unfortunatly, Profiler can't connect either, so I can't trace what's going on.
>>
>>Anyone have any suggestions on what to look at or tips on how to diagnose this problem?