Solved

error messages : spid180, spid1, spid2, spid4

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user 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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now