SQL Agent throwing TCP timeouts

rdoda

WE are running NPM v9.0 SP1 with dedicated SQL 2005 Standard edition server with 4 dual core 3GHz cpu, 64 GB RAM and 5X400 GB 10K RPM drives in RAID 5. We have 8 engines with total of

I am having these errors almost hourly in SQL agent logs:

error1.

 Date  11/25/2008 10:58:30 AM
Log  SQL Agent (Current - 11/25/2008 12:25:00 PM)

Message
[298] SQLServer Error: 258, TCP Provider: Timeout error [258]. [SQLSTATE 08001]

error 2:

Date  11/25/2008 10:58:30 AM
Log  SQL Agent (Current - 11/25/2008 12:25:00 PM)

Message
[165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]

error3:

Date  11/25/2008 10:58:30 AM
Log  SQL Agent (Current - 11/25/2008 12:25:00 PM)

Message
[298] SQLServer Error: 258, Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]

I do see that two of the 8 cpus are almost maxed out at >90% all day, while other 6 are usually <20%.

Average Disk Queue length is around 16.

DB size is abt 26 GB.

Total Monitored Nodes=8K

Monitored Interfaces=14K

vhcato

Wow! Average disk queue length around 16... That's not good. Do you have transaction logs and data on the same RAID 5 array? We had some odd SQL connection issues at one time when we were running on lesser hardware and even on better hardware when sharing an instance with other databases.

As far as the CPU usage, are you limiting SQL to just the two CPU's (perhaps for licensing limitations)? If you have 4 dual cores, and you licensed SQL for just one socket, then that would explain the 2 CPU's. I'm not sure why they would be at 90% consistently.