[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

error messages : spid180, spid1, spid2, spid4

hello experts. this is my question :
i have a sql 2000 with sp4 server running on windows server 2003 standard sp1. strong machine : 4 XEON cpu's, fast raid 5 disks, alot of free space!
the sql is crushing from time to time. when i look at the logs of the sql, i recieve error messages :
spid180: SQL Server has encountered 18 occurrence(s) of IO requests taking longer than 15 seconds to complete on file 'database_file.mdf' in database [database_name]. the os file handle is... the offset of the latest long IO is...

this error is olso shown as : spid1, spid2, spid4 , etc...

what can i do to fix this problem ?

thank you all
itay
0
itaybl
Asked:
itaybl
  • 3
1 Solution
 
NightmanCTOCommented:
Have a look at your disk queue lengths (from perfmon) and % disk time as well. I suspect that you have an IO bottleneck.

You can correct this by getting faster disks, changing RAID configurations (RAID1 or RAID1+0 will be much faster and more reliable in the event of a failure) splitting tempdb, transaction logs and data to different physical disks (or RAID containers) respectively.

Also, have a look at long running transactions with high reads and IO (use SQL Profiler and sp_who2) - you could probably do with some index and query redesign.

Also, your indexes may be fragmented - consider reindexing (off peak - this will lock your tables)
0
 
itayblAuthor Commented:
disk queue is near 0 . also the disk time.
diskd are 10000rpm. 4 disks in a raid 5 formation.
0
 
NightmanCTOCommented:
Check you hardware - perhaps you are having intermittent problems with one of the disks. Also, what is the disk queue length when the failure occurs, and what other processes are running at the time (e.g. backups)

Still, would recommend RAID1 min. for a fast SQL Server.
0
 
NightmanCTOCommented:
Hi itaybl

Have my comments helped you? If not, please can you provide some feedback, otherwise have a look at http://www.experts-exchange.com/help.jsp#hs5 for information on closing questions.

Regards
Nightman
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now