November 20, 2011 at 12:55 pm
Can anybody remind me (I think I saw info on forum) how to find page context (i.e. what's on page) using pageID? I found some bad pages from msdb..suspect_pages table but not sure how to identify what objects are there?
Info I mentioned looks like:
page_id event_type count last_update
1000001 1 140 11/19/2011 1:50 --OS errors other than checksum and torn pages
1000002 2 150 11/19/2011 1:40 --bad checksum
To confirm errors I would like to run dbcc checkdb with physical_only (or _no_onfomsgs) but still would like to know what resides on pages 1000001 and 10000002?
Thanks,
November 20, 2011 at 1:25 pm
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Post the full and complete results here.
Don't run physical_only, not enough is done.
Let's run that first, see what it says and how bad the errors are, then an appropriate plan can be made for recovery. CheckDB will also give the table and index that the pages in question belong to.
Depending on how bad the damage is, it may or may not be possible to view the pages at all, so CheckDB first and then decisions about how to proceed.
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
November 20, 2011 at 1:49 pm
Gail,
Thanks for your response- I run DBCC CHECKDB with no_infomsgs 2 times but both times failed after 8 min execution time ("Internal error- contact sys admin"- something like this)- I guess there is somewhere time limit for executing query from SSMS on Prod server. Therefore I'll try tomorrow from office again. Actually suspect_pages table indicates 5 sequential pages with event_type 1 or 2. I need to find what they (pages) comprise as Management/Dev team blaim us (DBA) for some appl error referring to this error (have no idea though how they got it):"SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xa5b4f207; actual: 0x9614684c). It occurred during a read of page (1:8313155) in database ID 13".
I checked suspect_pages table and ... the rest you know. I'll try to investigate EventViewer (or to find App error Log file)- but this is another story.
Thanks again for help,
Yuri
November 20, 2011 at 2:11 pm
Yuri55 (11/20/2011)
Gail,Thanks for your response- I run DBCC CHECKDB with no_infomsgs 2 times but both times failed after 8 min execution time ("Internal error- contact sys admin"- something like this)-
"something like this" doesn't help me diagnose the problem...
I guess there is somewhere time limit for executing query from SSMS on Prod server.
SSMS does not have timeouts, so there's nothing in SSMS that would terminate CheckDB part way through.
Actually suspect_pages table indicates 5 sequential pages with event_type 1 or 2. I need to find what they (pages) comprise as Management/Dev team blaim us (DBA) for some appl error referring to this error (have no idea though how they got it):"SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xa5b4f207; actual: 0x9614684c). It occurred during a read of page (1:8313155) in database ID 13".
You have a corrupt database. Sorting out who's to blame is not important, figuring out how to fix it is.
That error you list is caused when SQL tries to read a page that's damaged, so if that's the error the app got then the damaged pages are the cause. What caused them is probably a hardware fault somewhere (vast majority of corruption is)
What's the status of your backups? When was the last clean backup? What recovery model? How often are the log backups run?
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
November 20, 2011 at 4:05 pm
"What's the status of your backups? When was the last clean backup? What recovery model? How often are the log backups run?"
It seems to me that status is bad :angry:-
I found only this March backup- will try to clarify tomorrow
(I am new DBA in this company and have what I got). First error that I found from error log dated Oct. 24. Recovery mode- bulk-logged. To resume- status does not look good. Thanks
November 21, 2011 at 12:22 am
No, that doesn't look good at all. Post the CheckDB results (exactly what it returns) and I'll let you know how bad it is.
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
November 21, 2011 at 12:25 am
p.s. DO NOT restart the server. DO NOT detach the DB. Do not try any repair techniques that you may find for now. CheckDB results first, then a decision on how to proceed.
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
November 21, 2011 at 10:14 am
DBCC CHECKDB failed again with error: "Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services."
Need to investigate- Google returns some data. Thanks,
November 21, 2011 at 10:17 am
Yuri55 (11/21/2011)
DBCC CHECKDB failed again with error: "Msg 8967, Level 16, State 216, Line 1An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services."
Need to investigate- Google returns some data. Thanks,
Aside from Gail, there's only 1-2 better person on the planet to debug this and she knows how to get in touch with them.
Please post the full error message if you want real help.
November 21, 2011 at 10:25 am
Ninja's_RGR'us (11/21/2011)
Yuri55 (11/21/2011)
DBCC CHECKDB failed again with error: "Msg 8967, Level 16, State 216, Line 1An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services."
Need to investigate- Google returns some data. Thanks,
Aside from Gail, there's only 1-2 better person on the planet to debug this and she knows how to get in touch with them.
Please post the full error message if you want real help.
Unfortunately I cannot "post the full error message" regardless I really "want real help" :-)-
as I mentioned in my previous post DBCC CHECKDB cannot be completed-
I checked already some comments regarding this issue/error (including Paul Randal)- probably need to look for backup plan.
Thanks, Yuri
November 21, 2011 at 10:34 am
So you can't post the full error message because you can't get it or because you cannot post it (confidentiality)?
November 21, 2011 at 10:54 am
Yuri55 (11/21/2011)
DBCC CHECKDB failed again with error: "Msg 8967, Level 16, State 216, Line 1An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services."
That's a pretty clear 'restore from backup' error. This is not going to be pretty.
Since you have no usable backup...
Script all objects. (some may fail)
Export (bcp OUT) all data (some may fail)
Recreate the DB, recreate all objects, load all data.
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
November 21, 2011 at 10:58 am
Ninja's_RGR'us (11/21/2011)
So you can't post the full error message because you can't get it or because you cannot post it (confidentiality)?
Because CheckDB fails with that internal operation error and doesn't return anything else.
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
November 21, 2011 at 11:01 am
GilaMonster (11/21/2011)
Ninja's_RGR'us (11/21/2011)
So you can't post the full error message because you can't get it or because you cannot post it (confidentiality)?Because CheckDB fails with that internal operation error and doesn't return anything else.
I wasn't 100% sure. So I wanted to make sure you had the most relevant info possible upon your return.
Hopefully it didn't fall under wrong advice category. 😉
As always you ahve the floor on those cases, unless I'm 100% certain of the correct answer.
November 21, 2011 at 11:40 am
Thanks guys for help- I am looking for backup right now
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply