October 2, 2007 at 8:52 am
We have a database that is supported by a 3rd party
I have reponsibility for the application that uses the database, but have very little SQL knowledge. Normally no problems as the SQL database is 3rd party maintained.
After fixing a problem with a part of the system for which they needed a backup, we started seeing errors in the event log
"Event ID: 605 Attempt to fetch logical page (1:360362) in database 9 failed. It belongs to allocation unit 72057598691246080 not to 72057598731091968." and the database became corrupted after a few days.
The corrupted table was fixed from a backup, but ever since the other fix was done, we are still getting problems.
The maintenance plan is currently failing on "Check Database Integrity",
Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
" failed with the following error: "Page (1:226432) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:218376), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
Page (1:226456) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:218376), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 2 allocation errors and 0 consistency errors in database 'jcre_thankQ_live'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (jcre_thankQ_live).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly
We are also seeing the following error:
Event ID: 5000 SQLDumper
Message
EventType sql90exception, P1 sqlservr.exe, P2 2005.90.3054.0, P3 46049bfc, P4 sqlservr.exe, P5 2005.90.3054.0, P6 46049bfc, P7 0, P8 011a7bd9, P9 00000026, P10 NIL
Hardware has been checked and all OK
The 3rd party have advised against running DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS
SQL 2005 SP2 with all latest patches / updates.
Would be grateful for any ideas or pointers in the right direction. I can navigate SQL and check error logs and read Books Online, but that's about it.
Thanks
Nicola
October 2, 2007 at 10:27 am
Here's a great link to a bunch of troubleshooting you can try:
http://www.sqlskills.com/blogs/paul/CategoryView,category,Corruption.aspx
(as well as things to NOT do)
October 5, 2007 at 3:57 pm
Given that the corruption problems keep happening after they fixed it by restoring from a backup, I'd guess you have a hardware issue. Even though the hardware checks out - it can be a transient problem that doesn't manifest itself while diagnostics are running, and most diagnostics don't really stress the hardware much.
I recommend running SQLIOSim to see if it can flush out any hardware issues - see http://www.sqlskills.com/blogs/paul/2007/09/12/HowToTellIfTheIOSubsystemIsCausingCorruptions.aspx
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
October 5, 2007 at 5:22 pm
Paul Randal (10/5/2007)
Given that the corruption problems keep happening after they fixed it by restoring from a backup, I'd guess you have a hardware issue. Even though the hardware checks out - it can be a transient problem that doesn't manifest itself while diagnostics are running, and most diagnostics don't really stress the hardware much.I recommend running SQLIOSim to see if it can flush out any hardware issues - see http://www.sqlskills.com/blogs/paul/2007/09/12/HowToTellIfTheIOSubsystemIsCausingCorruptions.aspx
Read your own tags much? 😛 The link I gave has that article (along with a bunch of other ones that are great). :hehe:
October 5, 2007 at 5:27 pm
Yeah - I just wanted to point Nicola right to what I would do first.
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
October 8, 2007 at 6:54 am
I totally agree with Paul ( Not seen the link) this is a hardware issue. More info you can enable the trace flag 818. Please check the http://support.microsoft.com/kb/826433
Then go for SQLIOStress utility.
Minaz Amin
"More Green More Oxygen !! Plant a tree today"
October 8, 2007 at 8:46 am
Minaz - your advice would be correct if Nicola was running SS 2000 but she's on SS 2005, so page checksums and SQLIOSim are the way to go.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply