Why do I get error messages when my SQL Server is running under a heavy load.

Question

I am running SQL Server 2000 on Windows 2000 with 1GB of RAM on a dedicated server, but I am getting the following error message when doing backup and running simple queries like ALTER TABLE. What can be the problem?

Server: Msg 845, Level 17, State 1, Line 1
Time-out occurred while waiting for buffer latch type 4 for page
(1:8450), database ID 8.
Server: Msg 3013, Level 16, State 1, Line 1

Answer

I have also run into this same (well, very similar) error message on two different occasions. What the SQL Server is telling you is that the disk I/O system is not able to keep up with SQL Server. Almost always, this message means that you have a driver or hardware problem with your I/O system, and you need to identify the problem and fix it. This error can occur anytime SQL Server is generating a lot of I/O activity.

In one of the cases I found this error, in was with a SAN that was using an outdated driver. On another occasion, it was a physical problem with a SCSI card. Your cause may be the same, or very similar.

You, or a network administrator, needs to check the I/O subsystem and see what is going on. Many have error logs you can view to see if error are being generated.