Solved

error messages : spid180, spid1, spid2, spid4

Posted on 2006-11-15
6
392 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
  • 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Read about achieving the basic levels of HRIS security in the workplace.
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…

809 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