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?

 

[gmmastros]

If you can, I would suggest that you run SQL Profiler just before the daily lockup. Profiler should give you a pretty clear indication of what's going on.


-George

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

 

[JayKusch]

Is there any other services/processes running on the DB server?

Like IIS or another app or is the server strictly a DB server?

Thanks

J. Kusch

 

[brazilhills]

The server is just the db server. We have a web server that runs Apache to connect to this database. I don't think IIS is running on the db server but I'll check. If anything it should be running Apache for web services. The company that supports this application set it all up and has been monitoring but we are all kind of stumped by this one. Thanks for your post and if you have any ideas, regardless if it is a long shot we are open to suggestions.

 

[JayKusch]

Have you taken gmmastros suggestion and set a Profiler trace to run in the window you are seeing the issue/slowness?

Also, are there any hidden NT/Windows Scheduled Tasks running during this time?

Thanks

J. Kusch

 

[brazilhills]

Yes I sent that suggestion to the vendor and they said they were running it but had it narrowed down to what they were logging. They have changed it so that maybe it will catch what is happening. They are also loading something else that will monitor windows services on both the webserver and the db server to see if there is anything hidden that we weren't seeing. Thanks for your replies!

 

[MDXer]

are they running any backups at the time it locks up?

 

[JayKusch]

LOL ... he already covered that base ... "We have ruled out network port issues, antivirus, scheduled tasks, stored procedures, sql backups, file system backups"

Thanks

J. Kusch

 

[brazilhills]

No we have ruled out file system backups, sql db maintenance jobs, scheduled tasks as far as we can tell, no scheduled reports running, all the "normal" stuff. Thanks for asking and if you have any ideas please post.

 

[JayKusch]

Have you checked your NIC saturation during the time of stress?

Thanks

J. Kusch

 

[Mich]

Do you have 'Autogrowth' enabled on the db's in question? I've run into situations where this has caused problems similar to this.

-If it ain't broke, break it and make it better.

 

[brazilhills]

Well networking checked the ports. I do not know if they checked the nic card because we actually had an "incident" and had to move the hard drives from our original server to a new server. Problem still happens every day after moving to new server so it doesn't appear to be hardware related. I think they also checked the port on the switch. I can ask him if he checked saturation specifically though.
I also remember the vendor did check autogrowth and I want to say that it was fine but now I can't remember. I will ask again.
Thanks!!!

 

[brazilhills]

Oh and I should add that the vendor found some errors in the event viewer that suggested that we should apply a patch to the SQL. WE will be doing that at 4am. Please pray for us!

 

[JayKusch]

Also check your DB to make sure AutoConnect is not enabled.

Thanks

J. Kusch

 

[brazilhills]

I'll ask them thanks!!!

 

[brazilhills]

They asked do you mean AutoConnect or AutoCorrect?

 

[JayKusch]

Sorry ... misuse of words there ... I meant to say Auto Close.

Go to the DB properties and then options tab/section.

There is an option called "Auto Close" which by default is set to FALSE.

Thanks

J. Kusch

 

[brazilhills]

In response to the question about autogrowth, the vendor said Autogrowth is set to 10%.

 

[brazilhills]

Their response to Auto Close is it is not checked.

 

[Mich]

In response to the question about autogrowth, the vendor said Autogrowth is set to 10%.

Is that enough? The situation I ran into happened because 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.

-If it ain't broke, break it and make it better.