Error Fix: Msg 605 Attempt to fetch logical page (3:1307866) in database 6 failed. It belongs to allocation unit 72057594113359872 not to 72057594113490944

Msg 605, Level 21, State 3, Line 1

Attempt to fetch logical page (3:1307866) in database 6 failed. It belongs to allocation unit 72057594113359872 not to 72057594113490944.

Or

Msg 21, Level 21, State 1, Line 1

Warning: Fatal error 605 occurred at OCT  31 2011  6:08AM. Note the error and time, and contact your system administrator.

This error comes in red color is disguise, if you login with any user which is not sysadmin type user, you will see this kind of error in corruption of page(s), if you login with SA, Administrator or and other SysAdmin login, you will not see error comes in red color but the descriptive error given in the title

There was a festival holidays in our Indian office from 26^th to 28^th OCT 2011 (Wed/Friday) so obviously I was out of town and back after 5 days on 31^st OCT 2011. As soon as I come to the office and checked few of my email, I suddenly get complain that few pages in our software are throwing an error so I picked up the query which was there in those pages along with the same parameter they were providing and found the error given above.

 

As soon as I read this error, I came to know that there is a page corruption in database.  It may be due to heavy snow fall in NJ in October, they have got power failure there from few days and tried to keep the server up and running via power generator. As soon as Power Generator’s fuel get finished, everything gets down and after refuel, they starts everything again. I came to know this as soon as I come back from holiday. I think this is the Indian New Year gift to me.

I tried to gather some more information about the data file and page number given in error message with DBCC PAGE command.

dbcc traceon(3604)

dbcc page('MyDatabaseName',3,1307866,3)

dbcc traceoff(3604)

You can use option 0,1,2,3 as the last parameter (bold 3) of DBCC PAGE command.

So now I have two options.

1.)    Restore that page from full Database backup

2.)    Try DBCC commands and if needed, allow data loss

I HIGHLY RECOMMEND to go for 1^st option but if you are out of luck and don’t have last full backup, you must have to go for second way.

I have restored page from my last full database backup with below given TSQL.

RESTORE DATABASE MyDatabaseName
PAGE = '3:1307866'
FROM DISK = 'D:\MyDatabaseName.bak'
WITH NORECOVERY

Now, let us talk about second option if you don’t have full backup of your database. Use DBCC CHECKDB command.

--checking database's integrity and won't show so many informational message,

--it will only shows error messages and warnings.

DBCC CHECKDB ('MyDatabaseName') WITH NO_INFOMSGS

Once you execute above command, it will recommend you repair level. it may recommend REPAIR_REBUILD if you really lucky but if you will see repair level REPAIR_ALLOW_DATA_LOSS, you have to be ready to lose some of your data.

You may use either of the below given command based on the Repair Level you have been suggested.

1.)

ALTER DATABASE MyDatabaseName SET SINGLE_USER

GO

DBCC CHECKDB('MyDatabaseName', REPAIR_REBUILD)

GO

ALTER database MyDatabaseName SET MULTI_USER

GO

2.)

ALTER DATABASE MyDatabaseName SET SINGLE_USER

GO

DBCC CHECKDB('MyDatabaseName', REPAIR_ALLOW_DATA_LOSS)

GO

ALTER database MyDatabaseName SET MULTI_USER

GO

Moral of the story is, always have FULL recovery model for your database and schedule full/ transaction/ differential backup policy. This is a MUST DO for any DBAs. You never know, when will you need it!!!!

Reference: Ritesh Shah
 

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”