Post reply

SQL 2000 Server locks CPU @ 99%

  • February 27, 2004 at 3:11 pm

    #86235

    Hi,

    I am running Windows 2000 ES and SQL Server 2000 Enterprise.

    I have been running this server for about a year now, it has a quite low volume of traffic and it sits aside a Win2K IIS server, serving data for an ASP web site.

    For no easily visible reason (by that I mean there are no record locks or obvious traffic from the web server)  the CPU quickly shoots to 99% and stays there. I have run a trace log with simple CPU, SQL Record Locks, and memory, all seem very low at the time of CPU max out.

    Checking out the task manager, the entry for sqlservr.exe is 97-99%, the only way I can recover the machine is to reboot, I cannot restart the SQL service using the service manager or services.msc.

    The network switch that the server is connected to lights up like a Christmas tree flashing continuously on all live ports, our firewall is knocked out, similar to a Denial of Server attack.

    There is nothing in the event manager apart from usual entries after restarting and  nothing obvious (to a relative noobee) in the sql server logs.

    I would appreciate it if any one knows what this may be to post me back, or, if you think there are any significant perf counters to watch.

    Many thanks

    Peter

  • February 27, 2004 at 3:48 pm

    #496315

    Not trying to worry you excessively but are you auditing your logins and the like ? If not, set the auditing on Full and verify that you are not getting excessive connection attempts to the DB server.

    I once experienced a rather "witty developer" that was dumping databases, and other generally unacceptable behaviour to his desktop (dts packages) so he could "refresh" his personal "development environment", you should also check what processes (sysprocesses) are using the most resources at these times...by creating a stored proc that picked up the information and loaded this into a historical table to referesh over time.

    Otherwise have you gotprocesses are running local (db server) that are set to run at specific times (i.e. Index defrags orDBCC's or anything that utilizes cpu processing) ?

    Goodluck !

    Jim Babington

    jbabington@hotmail.com

    Jbabington
    Jbabington@hotmail.com

  • February 27, 2004 at 4:22 pm

    #496322

    Hi JBabington,

    Thanks for the reply, and the pointers.

    I am a SQL Server "know it not". That is, I have limited resources around me to be able to learn SQL Server very well. I have a good book and a lot of enthusiasm, and I usually gets things working.

    There is nothing new about the setup or situation, this box has been happily running the database for over a year, which is why I posted in here, out of frustration.

    There are only 2 users here,  running simple VB.net desktop applications, accessing the database for lookup and edit functionality. The only other accessing party of the users of our web site.

    There are some web services running from IIS on the same machine, which are regularly used during the day by the 2 mentioned above, I can see plenty of SPIDs for the same authentication select statement.

    I am not sure how to Audit properly or decipher log entries, which is something I must look into and become more educated on. The database backs up (dump style) through a maintenance plan which happens in the early hours of the morning.

    I cannot understand the maxing out on CPU and total blockage of the network, its like something is downloading the whole Internet through the switch, but it seems to be coming from SQL. Pulling the NIC cable usually stops it all !!!

    But I have to reboot to recover the machine.

    Peter

Viewing 3 posts - 1 through 3 (of 3 total)

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