Upgrading from SQL Server 2000 SP3 to SQL Server 2000 SP4

Tags:
SQL Server 2000
SQL Server 2000 SP3
SQL Server 2000 SP4
SQL Server errors
SQL Server upgrades
We have SQL Server 2000 SP3 running on Windows 2003 Server. Every week, after running our optimization and integrity checks on Sunday, we get this error: Time out occurred while waiting for buffer latch type 2... This happens several hours into the day every Monday. Would upgrading to SQL Server 2000 SP4 resolve this issue?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Try this…

http://support.microsoft.com/kb/q309093/

————

Buffer timeouts are usually caused by slow disk problems, or disk queueing. The root cause can include a failing disk, or simply disks that are no longer fast enough to keep up with the current disk load.

Check perfmon for the Current and Average disk queue, as well as the number of IOs / sec that the disks are performing. A good number to work off of is 100 IOPs per second per spindle for fast SCSI or fibre channel drives. If you are using SATA or IDE then 60-80 IOPs per second per spindle will be a closer number.

I think that you will find that your maintenance plans is pushing the disks, and as Monday comes around and data is being destaged from memory and new data is being loaded from disk into memory a great amount of stress is being placed on the disks.

Unfortunately SQL 2000 SP4 probably will not fix this problem, but it is a good idea to install it anyway as it contains many fixes to SQL Server 2000 SP3.

———-
Agree that SP4 is unlikely to resolve this issue. Also, the KB above refers to an issue that was fixed pre-SP2 (8.00.532) and the OP is already on SP3.

If you are pretty comfortable with perf tuning, setup perfmon to track disk and query activity, specifically to look for bottlenecks and unusually long durations. Also, look at your waitstats to see what specific resources you are getting a more than expected queuing. The another responder has indicated, it may very well that be the disk subsytem is overwhelmed and the fact that you see this after some busy maintenance operations leans further in that direction. If perfmon and waitstats data indicate disk bottlenecks, that’s pretty easy to solve (if you can get and install more disks).

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,010 pointsBadges:
    report
  • Pressler2904
    I know that those of you who read this side regularly have seen me say this before: Are you using SCSI disks? If so, UPDATE THE FIRMWARE ON THE DRIVES. They all need to be updated and at the same rev level or you run a far higher chance of disk or buffer timeouts, up to and including RAID array members "falling off" the bus and being marked as bad. It's simply a case of making sure your foundation is solid before building a house. If you are running IDE or SATA disks, this will not be an issue, and if you've already performed this check and addressed any issues found, then (obviously) it's something else.....
    2,190 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following