August 18, 2009 at 9:30 am
I am sporadically receiving the following allocation error for tempdb when a stored proc runs that uses a cursor and temp tables:
Attempt to fetch logical page (1:208) in database 2 failed. It belongs to allocation unit 422212465786880 not to 1369094287459680256.
Error: 605, Severity: 21, State: 3.
I've considered changing the temp table to a permanent table but was hoping there might be a setting in tempdb that I could adjust rather than touch the code. I have not found much info on this for SQL Server 2008.
Thanks in advance!
August 18, 2009 at 12:55 pm
Does restarting the instance fix that?
If not, can you please run the following and post any output that it returns
DBCC CHECKDB ('Model') WITH NO_INFOMSGS, ALL_ERRORMSGS
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
August 18, 2009 at 1:14 pm
The dbcc checkdb on model produced no errors.
Restarting SQL Server did not have an immediate effect. I had to run the stored proc several times before it would run. The stored proc is kicked off in a SQL job from SQL Server 2005. It doesn't consitently have issues.
I was considering adding another datafile to tempdb to see if that would help alleviate the issue.
August 18, 2009 at 1:45 pm
I'm going to consult an expert on this one. Please be patient...
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
August 19, 2009 at 9:23 pm
Sorry - the expert took a while to read email 🙂
Why did you CHECKDB model?
Are you running queries using NOLOCK or read-uncommitted isolation level?
Can you post the T-SQL of the query that experiences the error? Are you rebuilding an index on the temp table right before a select in the same batch?
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
August 20, 2009 at 1:51 am
Paul Randal (8/19/2009)
Why did you CHECKDB model?
I asked him to just to be sure that there wasn't some corruption in model that was coming across when TempDb's recreated. Purely precaution.
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
August 20, 2009 at 12:30 pm
Before the select, the stored proc created runs a number of cursor. Then via a linked server selects recoreds from another database and inserts. This is the point where it failed. There is no index building w/ this stored proc.
The stored proc has since been rewritten to use a permanent table and now I no longer get the allocation errors. I'm still curious why I started getting these errors when it had been running fine for sometime. Any ideas what would cause this? I never got any allocation errors when this same proc ran on SQL Server 2005.
August 20, 2009 at 12:55 pm
You've hit a known bug that was just discovered last week. The SQL team is aware of it and it will be getting fixed soon.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
November 23, 2009 at 4:31 am
Paul,
I am having the same issue, but can not find any information about the fix you mentioned. Can you provide more details about the fix.
As a workaround I implemented method 2 from http://support.microsoft.com/kb/916086.
"Disable the Auto Create Statistics option and the Auto Update Statistics option in the tempdb database. "
This fixed it for now, but I'm not too happy about this option.
Thanks,
Brian
November 23, 2009 at 2:17 pm
I don't think it's been fixed yet.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
May 14, 2012 at 12:24 pm
Anyone know of a fix for this yet? I'm facing this on SQL 2008 (SP2), and I'm curious if this is fixed in SP3.
January 24, 2014 at 11:25 am
Check this out might help
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply