SQL server not responding

Question

Post reply

SQL server not responding

Andrew J

Mr or Mrs. 500

Points: 507
More actions
April 3, 2006 at 1:58 pm

#112785

Hi All,
I have SQLserver2000 installed on Intel (R) Xeon Dual processor 3.20 GHz with 2 GB of RAM.
This sql server have: 1075 Database, aproximately it handle 172 process, 151 locs/proc ID, 63
Locks/objc.
sometimes, the sqlservr.exe proces use up to 99% CPU usage. at this point, the SQL server become
not responding. If it access from .Net programm, it will gave error:
"System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding...."
this can occur for 30 to 60 minutes.. after that, it came back normal again.
Does anybody have expirience this? is there any way to trace what makes the sqlservr.exe use 99%
CPu process? (i have try to check the logs, but did not able to find anything suspicious... maybe
i missed something... please help )
And also, is the server specification can handle up to 1000 Database? What do you guys think of
this?
Please advise.
Thank you very much for your response & knowledge share

Best Regards,
Andy J.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 1

What is: 1075 Database

What is: 151 locs/proc ID

What is: 63 Locks/objc.

And what is: server specification can handle up to 1000 Database

It sounds like you have an application that is walking all over itself so hard that it is keeping your server busy with locking, if these things you've said mean what I suspect they do. If you have 151 locks per process ID, and 63 locks per object, it's no wonder your processes are taking forever, causing timeouts, etc....track down what's causing the locking, and eliminate it to start with. I would suggest getting a dba to help you.

SQLBill SSC Guru Points: 51440 More actions · Answer 2

You can have 32,767 databases. So, 1075 isn't maxed out. However, it's still a lot of databases and if all of them are being used at the same time, or if there are several 'heavy' transactions occuring, the CPU can get maxed out.

-SQLBill

rshostac Old Hand Points: 301 More actions · Answer 3

We also had an incident like the one you are seing where, we weren't dealing correctly with database expansion and fragmentation or tables/indexes and we were getting the above issue while the database was expanding to take in transactions.

this all started after we installed SP4 and autogrow stopped functioning correctly.

Good luck

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 4

Be sure tempdb is not growing under the load. While it grows, writes and work stop in that db, which can lead to non-response on the other dbs that use it.

Andrew J Mr or Mrs. 500 Points: 507 More actions · Answer 5

Hi All,

Thank you very much for your response...

However, I still can't figure how to solve this issue...

May I know, how to clear/fix the Locks/objc thing.. I mean, it is not normal isn't it?

Thank you

Regards,

Andy J.

-StillConfuseHowToManageIt-

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 6

Normal is relative to your server. What might hang your server works great on mine.

You need to look at a bigger picture and not worry about that counter. Find out what appears to be slowing the server. Is it not responding to anything (could be tempdb growing)? Is it particular connections (which can be blocking) or something else?