April 8, 2016 at 12:56 am
HI All,
I came across an issue in production issue
“Msg 605, Level 21, State 3, Line 151
Attempt to fetch logical page (1:233168) in database 2 failed. It belongs to allocation unit 3458765197321961472 not to 5044032298514776064.
This issue came up when I run a Stored Procedure which has a lot of temporary tables involved
When I check Database id = 2, it is Tempdb .
I am wondering If I restart SQL server will fix this ?
also I cant think of any cause of tempdb corruption if this is the case
More information is I did SQL migration last week to a new server and tempdb is 8 MB as the initial size
Any feedback are really appreciated
Thank you
April 8, 2016 at 2:59 am
Not real corruption.
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
April 8, 2016 at 11:00 pm
This problem could be an issue with the database page or a problem with the SQL Server engine when determining what the expected allocation unit should be.
April 9, 2016 at 5:18 am
johnwalker10 (4/8/2016)
This problem could be an issue with the database page or a problem with the SQL Server engine when determining what the expected allocation unit should be.
It's a bug, see the link posted.
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
April 10, 2016 at 4:03 pm
From the last comment by that link:
the error does not appear if you add a clustered index to the temp table in question
_____________
Code for TallyGenerator
April 10, 2016 at 5:56 pm
Thanks for the reply Gail . Really appreciate it .
Regarding Service Pack , after the server migration I don’t apply any SP to that server ( Previously yes it has SP ).
The SQL Version is SQL server 2008 R2 . So do you reckon if I apply SP3 ( the latest SP I believe) it will remove the bug ?
Thank you
April 11, 2016 at 2:38 am
Put the latest service pack on. There's no reason to be running RTM of SQL 2008 R2.
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
April 12, 2016 at 9:43 pm
OK. Will do ,thanks !
April 21, 2016 at 7:56 pm
Hi Guys ,
I just applied SP3 yesterday nite and also restart SQL server instance in order to re-create temp files
But unfortunately I still have this issue .
Msg 605, Level 21, State 3, Line 151
Attempt to fetch logical page (1:32001) in database 2 failed. It belongs to allocation unit 422427667136512 not to 216172850355044352.
I notice old temp files are still there ( so after SQL migration – we install SQL server therefore new temp files are automatically created ) apart from that we also migrated the old temp files .
I am wondering that it could be a problem ?
I did the same thing in testing server actually but the report is working fine
Any feedback are really appreciated
April 21, 2016 at 11:30 pm
Regarding this issue I just enable traceflag ? dbcc traceon(4135)
And surprisingly I got different error “Parent tag ID 4 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set”
How do I deal with this issue ?
Any feedback are much appreciated !
April 22, 2016 at 3:18 am
Ah, yes. Forgot about that. You have to patch and enable the traceflag for the incorrect allocation page problem to go away.
As for the XML, no idea. Have you checked the queries using FOR XML EXPLICIT for accuracy? It's quite hard to get right.
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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply