Every so often one of my instances of SQL Server 2000 frequently keeps using 45-50% CPU (Every 2-3 secs.)
After a reboot things go back to normal for a period of weeks of months, then starts again.
Windows 2000 Server SP4 (DHCP,DNS, SQL,File, Print)
SQL Server 2000 SP4 x 2 instance , MSDE x 1
4 GB RAM (Avg MB available = 775MB)
6 SCSI HDDs (RAID 5)
As far as I can tell from Performance Monitor, the machine is handling the workload easily.
SQL Profiler also doesn't report any reason for the frequent peaks. (That I can tell)
SEM doesn't show any significant locking. (As far as I know)
In the past I have been able to link this problem to NTDBSMGE.EXE from Pervasive.
Pervasive is still installed on the machine, but has no activity and no CPU usage.
Has any one got any idea's about this?
How can I tell which instance is the problem instance and how can I find and resolve the issue?