?
Solved

error messages : spid180, spid1, spid2, spid4

Posted on 2006-11-15
6
Medium Priority
?
405 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 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

801 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