Solved

error messages : spid180, spid1, spid2, spid4

Posted on 2006-11-15
6
393 Views
Last Modified: 2009-07-29
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
Comment
Question by:itaybl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
6 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 17947700
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
 

Author Comment

by:itaybl
ID: 17947809
disk queue is near 0 . also the disk time.
diskd are 10000rpm. 4 disks in a raid 5 formation.
0
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 17947858
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
 
LVL 29

Expert Comment

by:Nightman
ID: 18197941
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Querying data from 3 SQL tables 2 45
sql script with strange password string 1 46
Append Query Access 2010 4 26
Need quicker response from an Execption table 11 24
Read about achieving the basic levels of HRIS security in the workplace.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question