Server: Msg 845, Level 17, State 1, Line 1

[7280]

Hi,
I have a job that does a backup of all databases in a clustered instance.
Database version is sql 2000 sp3a.
From three days the backup is failing and the error is on one database. All other backups are fine.
This database reside on the same directory and disk of the other databases. Also the backup is done on the same disk of all databases.
I tried to manually backup this database as follows:
backup database ge to disk = 'C:\temp\ge.bak'
The error is:
Server: Msg 845, Level 17, State 1, Line 1
Time-out occurred while waiting for buffer latch type 3 for page (1:23983), database ID 40.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

From my point of view the only explanation is that the database has some corrupted blocks.
As I told all other databases backup are fine.
Have you ever encountered this error?

Thanks.

 

[7280]

I forgot to say that I ran a consistency check and it seems ok.
Is it possible that something is corrupted even if result of dbcc is ok?

dbcc checkdb ('ge') with physical_only
result is:
DBCC results for 'ge'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'GE'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

[ptheriault]

Have you run DBCC CHECKDB? Did it report any errors? If this is a production database and is very large you should wait till off hours to run CHECKDB.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Ok, so checkdb didn't return any errors. does the job history have the same error that you got from QA?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[7280]

The log of event viewer is generic, it simply says that it fails to complete backup, that's why I tried to run it manually.
Error is:
3041:
BACKUP failed to complete the command backup database ge to disk 'C:\temp\ge.bak'

Thanks.

 

[ptheriault]

if you right click on the job and select view job history there might be a better error message. Make sure you expand the job history to the step that fails. Also check the SQL Server log in Enterprise Manager.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

I found this for you. It might help.

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.

http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?topicid=1&faqid=20

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[7280]

The error in job history is:
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

I also found that faq in sql-server-performance.com before opening this post but I'm having the error only on this database. All other databases of the instance are ok. And as I previously told all databases reside on the same disk and all backup to the same disk. I also tried to backup on another disk but the error persists.

It seems related only to this database.

Thanks again.

 

[7280]

I found also this post:

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=7152

But again I'm having problem only on this database, not on all databases of the instance.

 

[ptheriault]

The error in that link is not the same error you are getting. I don't think your problem is related to permissions. I found this post which is very similar to the other post I found. I don't believe it matters that it is only one database failing. When you are tying to backup the database is your tempdb or anyother database experiencing an autogrow? What is your memory usage. This buffer latch error suggest that there is not enough free memory. Any chance that you could restart the SQL services before the next backup?

On very rare occasions, you may get an error similar to this from SQL Server:

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 845: [Microsoft][ODBC SQL Server Driver][SQL Server]Time-out occurred while waiting for buffer latch type 3 for page (2:0), database ID 15.

This error message may also be accompanied by additional error messages, such as a file log or database backup that failed.

This error indicates that your disk I/O system is being overloaded and that SQL Server timed out when trying to write data to disk (via the OS). If this error is a rare event, then there is no need to worry.

But if this event becomes frequent, it could mean that your disk I/O subsystem has hit a bottleneck that it can't overcome, or that your disk I/O subsystem itself is having problems. You need to find out which one of these is the most likely issue, and then deal with it accordingly.

One of the things that you might want to look for is what I/O activity was happening at the time of the error. For example, it is possible that a disk backup, a DBCC command, or other database activity was all happening at the same time, overwhelming the disk I/O. Perhaps the solution to the problem is as simple as rescheduling some scheduled tasks. [6.5, 7.0, 2000] Added 2-3-2003

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[7280]

I still think it's a problem of this db.
I ran the backup manually on another db in the same moment, and only this one fails.

backup database ge_wrk to disk = 'c:\temp\ge_wrk.bak'
backup database ge to disk = 'c:\temp\ge.bak'

Processed 272 pages for database 'ge_wrk', file 'GE_WRK_Data' on file 2.
Processed 1 pages for database 'ge_wrk', file 'GE_WRK_Log' on file 2.
BACKUP DATABASE successfully processed 273 pages in 0.403 seconds (5.531 MB/sec).
Server: Msg 845, Level 17, State 1, Line 2
Time-out occurred while waiting for buffer latch type 3 for page (1:23983), database ID 40.
Server: Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormal


I can't restart the instance, I should schedule it during the night.

Thanks again.

 

[ptheriault]

You tried to backup two db's at the same time? Can you run this one by itselft?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[RobertT687]

See:

http://support.microsoft.com/kb/898709/en-us

Mentions this message. Resolution is to install SP4.

A work around to this problem is to restart the SQL Server service.

 

[7280]

Yes I tried to run both backups at the same time (I created two maintainance plan scheduled in the same moment) and I'm having an error only on this database.

Thanks Robert for the link, it seems to be my problem. Since it seems a latch problem I was thinking to put database offline and then online. It's just a test.

Thanks again.

 

[7280]

It was not possible to put database offline, same latch error occured.