Sql db server locks up daily at same time

[brazilhills]

We have users using a web application that locks up daily. They access the application via a web server that connects to sql 2000 db server. If you are on the db server at the time of the slowness/lock ups you can still move around okay. If you are on the webserver and try to connect remotely to the db server during this time you cannot access it.
We have looked at all the normal networking stuff. We have looked at all the sql stuff and ran extra maintenance plans to see if database problems. We can't find anything.
We can tell via the monitoring software that the server is definitely busy at this same time daily. Just can't figure it out.
We have ruled out network port issues, antivirus, scheduled tasks, stored procedures, sql backups, file system backups, scheduled reports. We have done this for both webserver and db server.
Has anyone had this happen same time every day? If so what was causing your issue?

 

[brazilhills]

I will pass this question to the vendor. I'm not a DBA. When they are monitoring here is what they see. The application is written in PERL. They said they see PERLs stacking up on the webserver at this time which indicates a failure to communicate with the db server. Our networking people say they are not seeing the communication dropping but they see a spike in cpu. You also cannot remote into the db server during this locking up. If you are on the server when it happens it doesn't appear that anything is out of the ordinary.
I will pass this last question along and see what they say.

 

[brazilhills]

The vendor reinstalled SQL 2000 SP 4 then applied the patch. I don't want to jinx myself so I'll just say that I didn't get a call from the ED at the regular time like I have had every day for the last 3 weeks. Keep your fingers crossed.

 

[yelworcm]

My money is on the autogrowth. For a database of only a few hundred MB, this should be set to a flat rate of growth. Consider, at 1GB, the system will have to format 100MB of space on the disk for the autogrowth increment. I would set it to something a little more sane like 20 MB. SQL 2005 is considerably nicer during autogrowth, but SQL 2000 had problems.

 

[brazilhills]

After reinstalling SQL 2000 SP4 and applying the patch for this, we now have two times of significant lockups.
Does autogrowth only happen at a certain hour of the day or certain times of the day? We have a 13+ gb database. And not only is the application inaccessible but the entire db server is inaccessible for a few minutes during these lockups.
I will let the vendor know you all are voting on autogrowth setting.

 

[gmmastros]

For what it's worth, I don't necessarily think it's an auto grow problem. A database will only autogrow when it needs to. So.... suppose your db is 13 gigs, and you have room to spare. Then, you start adding rows to the table. At some point, you will not have enough room and the database will auto grow. Databases do not auto-grow on a set schedule.

In my opinion, the only way that autogrow would be causing this problem is if you were loading a lot of data just prior to the lock ups.

Look at the response from Mich @ 19 May 09 9:45

large amounts of data was being added to the db between 3 and 4 pm every day. The db had been set to a small initial size and Autogrowth was set to 2% (don't ask me why). That meant the db was bogging down every day between 3 and 4.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom