September 22, 2015 at 4:23 am
"Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)"
An application process was failing with "605 Fatal error".
The SQL Server errorlog had many of the 2 rows below over the previous 3 days. They had occurred 2-3 times, every 3-5mins:
The Windows Event Log also revealed all emanated from a specific SQL Server Login.
2015-09-15 10:01:39.400 spid98 Error: 605, Severity: 21, State: 3.
2015-09-15 10:01:39.400 spid98 Attempt to fetch logical page (3:424) in database 2 failed. It belongs to allocation unit 5693234778150207488 not to 7638787673942261760.
Database 2 is TEMPDB.
DBCC CHECKDB was run on Application DBs and TEMPDB with no errors reported.
I had looked at these 2 hits with the second one pointing to FREEPROCCACHE although it was for a SHRINK:
and
I decided as a last resort, before doing a server reboot or SQL Server cycle, to use DBCC FREEPROCCACHE. https://msdn.microsoft.com/en-us/library/ms174283(v=sql.105).aspx
This cleared the issue immediately. No recurrence of the above error messages and the failing application process resumed successfully.
Note: http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx has this text linking FREEPROCCACHE and TEMPDB:
“The cached object is scoped to the query plan that references it. If the plan is evicted from cache for any reason (perhaps by ALTER or DROP PROCEDURE or an explicit DBCC FREEPROCCACHE command) a background thread removes the tempdb object. “
September 22, 2015 at 8:40 pm
Joe Docherty (9/22/2015)
An application process was failing with "605 Fatal error".The SQL Server errorlog had many of the 2 rows below over the previous 3 days. They had occurred 2-3 times, every 3-5mins:
The Windows Event Log also revealed all emanated from a specific SQL Server Login.
2015-09-15 10:01:39.400 spid98 Error: 605, Severity: 21, State: 3.
2015-09-15 10:01:39.400 spid98 Attempt to fetch logical page (3:424) in database 2 failed. It belongs to allocation unit 5693234778150207488 not to 7638787673942261760.
Database 2 is TEMPDB.
DBCC CHECKDB was run on Application DBs and TEMPDB with no errors reported.
I had looked at these 2 hits with the second one pointing to FREEPROCCACHE although it was for a SHRINK:
https://social.msdn.microsoft.com/forums/sqlserver/en-US/60864b75-44fb-47a9-b3a1-3bfc4b3dac03/attempt-to-fetch-logical-page-in-database-2-failed
and
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2a00c314-f35e-4900-babb-f42dcde1944b/dbcc-shrinkfile-page-411283400-could-not-be-moved-because-it-is-a-work-table-page?forum=sqldatabaseengine
I decided as a last resort, before doing a server reboot or SQL Server cycle, to use DBCC FREEPROCCACHE. https://msdn.microsoft.com/en-us/library/ms174283(v=sql.105).aspx
This cleared the issue immediately. No recurrence of the above error messages and the failing application process resumed successfully.
Quick question, what is the full output of SELECT @@VERSION;
😎
September 23, 2015 at 1:08 am
Good spot!
I have removed one or two unnecessary details from the result of the SELECT.
"Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)"
Joe
September 23, 2015 at 1:58 am
Joe Docherty (9/23/2015)
Good spot!I have removed one or two unnecessary details from the result of the SELECT.
"Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)"
Joe
Next questions, any reason for not applying Service Pack 3 and the latest CUs?
Is page verification option set to PAGE_VERIFY=CHECKSUM ?
😎
There are two flavors of MSG 605, severity 12 which is caused by dirty read (nolock) and severity 21 which is an unresolved data page to allocation unit issue. If it persists you may want to contact MS support.
September 23, 2015 at 2:59 am
Known bug.
https://support.microsoft.com/en-us/kb/960770
It's not actual database corruption, the page_verify setting is immaterial. Test the traceflag which the connect item mentions, see if that fixes things.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2015 at 5:20 am
PAGE_VERIFY is set to CHECKSUM. Though I have just now read Gail’s update (thanks).
Re down level software - suffice to say, MS15-058 introduces urgency and will be actioned with necessary pre-requisite Service Packs, etc first of course.
Gail, thanks for your comments and references. Your first web reference is interesting – hotfix and TF. This instance is 2008 R2 SP1. I will re-read soon. Your interesting second one refers to creating a Clustered Index. Other similar-ish advice is in the second hit I detailed. It indicates “The temp table had an identity column and I neglected to make the column a Primary Key“. The relevant team are aware.
Many thanks to both of you for your time. Appreciated.
Joe
September 24, 2015 at 5:44 am
Hi,
DBCC Freeproccache also resolved this 605 error.Any idea on this? I understand that patch apply fix this and also adding column with identity and clustered indexes.
Thanks,
Deepak.
February 5, 2016 at 12:07 am
With user try to login with ID that is not Sys admin type user then MSG 605 occurs. This error Msg comes in red color the user comes across with this error when they try to login SA, Administrator or any other SysAdmin login. Error is demonstrated below;
Msg 605, Level 21, State 3, Line 1
Attempt to fetch logical page (1:224) in database 11 failed. It belongs to allocation unit 72058253744865280, not to 72057594040287232
There are two levels of severity in this error Level 21 and Level 12.
If severity Level is 21, the user session is detached and error is sent into SQL error log and Windows Application Event Log as 605 Event ID.
If severity level is 12, then it only results in error returned to client application. In this error message, allocation unit is an ID that determines index and object to which the page relates.
To solve both level of errors do as follows;
In severity 21:
The page might be damaged or incorrect and should be retrieved from backup but if you are unable to restore using backup then repair it using DBCC CHECKDB.
Verify hardware or the system level problems
Make sure that the PAGE_VERIFY=CHECKSUM database option is switched on. You may even receive error Msg 605 after enabling checksum, checksum can provide you with ultimate proof regarding page modification within the I/O system.
In severity 12:
•Avoid uncommitted isolation level (or NOLOCK hint)
•Prepared for retrying the query till the error was not faced by you
•Do not make changes to the affected table (s) during running the query.
If above method do not help ypu them you can opt for SQL Database File Repair Tool. SQL Repair Tool is the most trustworthy tool to fix error in SQL Server Database files. It also recovers corrupted and damaged database files without backup. It also recovers views, indexes, tables, stored procedures, unique/primary keys etc. that are saved in your damaged SQL database
February 5, 2016 at 1:06 am
With user try to login with ID that is not Sys admin type user then MSG 605 occurs. This error Msg comes in red color the user comes across with this error when they try to login SA, Administrator or any other SysAdmin login. Error is demonstrated below;
Msg 605, Level 21, State 3, Line 1
Attempt to fetch logical page (1:224) in database 11 failed. It belongs to allocation unit 72058253744865280, not to 72057594040287232
There are two levels of severity in this error Level 21 and Level 12.
If severity Level is 21, the user session is detached and error is sent into SQL error log and Windows Application Event Log as 605 Event ID.
If severity level is 12, then it only results in error returned to client application. In this error message, allocation unit is an ID that determines index and object to which the page relates.
To solve both level of errors do as follows;
In severity 21:
The page might be damaged or incorrect and should be retrieved from backup but if you are unable to restore using backup then repair it using DBCC CHECKDB.
Verify hardware or the system level problems
Make sure that the PAGE_VERIFY=CHECKSUM database option is switched on. You may even receive error Msg 605 after enabling checksum, checksum can provide you with ultimate proof regarding page modification within the I/O system.
In severity 12:
•Avoid uncommitted isolation level (or NOLOCK hint)
•Prepared for retrying the query till the error was not faced by you
•Do not make changes to the affected table (s) during running the query.
If above method do not help ypu them you can opt for SQL Database File Repair Tool. SQL Repair Tool is the most trustworthy tool to fix error in SQL Server Database files. It also recovers corrupted and damaged database files without backup. It also recovers views, indexes, tables, stored procedures, unique/primary keys etc. that are saved in your damaged SQL database
February 5, 2016 at 1:42 am
Because it has to do with certain plan forms. Clear the plan cache and the next compile may generate a different for. It's not a fix though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply