SQL Server detected a logical consistency-based I/O error

Starting to see a lot of this in the event viewer. This first popped up on 6/5/2015. There are several other databases on this server, how can I check this particular database out for issues?

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:175664120; actual 0:0). It occurred during a read of page (1:175664120) in database ID 24 at offset 0x00014f0d7f0000 in file 'F:\PFTest\PFT_Database\PFTestKC3.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
LVL 2
stlhostAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
Connect SSMS and run the DBCC command.

Use <database name>
go

DBCC checkdb()

or

DBCC CHECKDB('<database Name>')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Deepak ChauhanSQL Server DBACommented:
File location showing in error log is 'F:\PFTest\PFT_Database\PFTestKC3.mdf' so assuming this will be PFTest database.
0
Lee SavidgeCommented:
Sounds to me like a dodgy hard disk. Might need to replace them soon. Run the DBCC CHECKDB command for the database and see what it returns. Anything in the Windows event log?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

PadawanDBAOperational DBACommented:
Only because I like to be as specific as possible with my commands and I also don't like verbosity (is that a word?):

dbcc checkdb( 24 ) with no_infomsgs, all_errormsgs;

Open in new window


Assuming the corruption is still there, your output will give you some pretty useful information around the pageID that is corrupt.  If you want some additional help with that, post the info here!
0
stlhostAuthor Commented:
It's running just waiting for the output. This is a virtual server. The vmdk is about a TB with other DBs on it so this would not be good if its corrupted. (the whole thing) but I would assume Id see other errors.. waiting to see
0
PadawanDBAOperational DBACommented:
Optimistically, the object ID will be for a nonclustered index that you can just rebuild to resolve.  While you're waiting, you may also want nail down where your backups are.  Additionally, I would schedule to have checkdb running on all over your databases ideally daily, but at worst weekly.  If you want to start looking into this now, you can start with:

dbcc traceon( 3604 );
dbcc page( 24, 1, 175664120, 3 );

Open in new window


You'll be looking for:
    Metadata: ObjectID = xxxxxxxxx
    Metadata: IndexID = x


Then you can find the table:
select 
     schema_name( schema_id ) as SchemaName, 
     name, 
     type_desc 
from 
     sys.objects 
where 
     object_Id = xxxxxxxxx

Open in new window



Also.  What is the indexID? (1 is your clustered index = no good)
0
stlhostAuthor Commented:
PadawanDBA It's still running. Since its during the day it may take a while I guess. The results from your query

BUFFER:


BUF @0x00000001DAFCB680

bpage = 0x00000001DA2DA000           bhash = 0x0000000000000000           bpageno = (1:175664120)
bdbid = 24                           breferences = 3                      bUse1 = 47884
bstat = 0xc20809                     blog = 0x159a2159                    bnext = 0x0000000000000000

PAGE HEADER:


Page @0x00000001DA2DA000

m_pageId = (0:0)                     m_headerVersion = 0                  m_type = 0
m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 0      m_indexId (AllocUnitId.idInd) = 0    Metadata: AllocUnitId = 0
Metadata: PartitionId = 0            Metadata: IndexId = -1               Metadata: ObjectId = 0
m_prevPage = (0:0)                   m_nextPage = (0:0)                   pminlen = 0
m_slotCnt = 0                        m_freeCnt = 0                        m_freeData = 0
m_reservedCnt = 0                    m_lsn = (0:0:0)                      m_xactReserved = 0
m_xdesId = (0:0)                     m_ghostRecCnt = 0                    m_tornBits = 0

Allocation Status

GAM (1:175352576) = ALLOCATED        SGAM (1:175352577) = NOT ALLOCATED   
PFS (1:175663272) = 0x40 ALLOCATED   0_PCT_FULL                           DIFF (1:175352582) = CHANGED
ML (1:175352583) = NOT MIN_LOGGED    
Msg 2514, Level 16, State 5, Line 2
A DBCC PAGE error has occurred: Invalid page type - dump style 3 not possible.

Open in new window

0
PadawanDBAOperational DBACommented:
Can you try:

dbcc page( 24, 1, 175664120, 0 );

Open in new window


That header is kinda fubar'ed.  If we can't get any good information out of that page header, the only other option that I can think of (someone may know of a better way) would be to iterate through every single index to find that pageID.  Hopefully that won't be necessary.
0
stlhostAuthor Commented:
I'm an SQL amateur so I'm not sure what I am looking at. Here is the results of that query

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (0:0)


BUFFER:


BUF @0x0000000132FDF400

bpage = 0x00000001327D0000           bhash = 0x0000000000000000           bpageno = (1:175664120)
bdbid = 24                           breferences = 0                      bUse1 = 48561
bstat = 0x1c20809                    blog = 0x159a2159                    bnext = 0x0000000000000000

PAGE HEADER:


Page @0x00000001327D0000

m_pageId = (0:0)                     m_headerVersion = 0                  m_type = 0
m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 0      m_indexId (AllocUnitId.idInd) = 0    Metadata: AllocUnitId = 0
Metadata: PartitionId = 0            Metadata: IndexId = -1               Metadata: ObjectId = 0
m_prevPage = (0:0)                   m_nextPage = (0:0)                   pminlen = 0
m_slotCnt = 0                        m_freeCnt = 0                        m_freeData = 0
m_reservedCnt = 0                    m_lsn = (0:0:0)                      m_xactReserved = 0
m_xdesId = (0:0)                     m_ghostRecCnt = 0                    m_tornBits = 0

Allocation Status

GAM (1:175352576) = ALLOCATED        SGAM (1:175352577) = NOT ALLOCATED   
PFS (1:175663272) = 0x40 ALLOCATED   0_PCT_FULL                           DIFF (1:175352582) = CHANGED
ML (1:175352583) = NOT MIN_LOGGED    


DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Open in new window

0
PadawanDBAOperational DBACommented:
No problem!  Corruption is a pretty advanced topic to resolve.  Here's an example of what you *should* be seeing:

PAGE: (1:5155)


BUFFER:


BUF @0x00000001813EF900

bpage = 0x000000015BABC000          bhash = 0x0000000000000000          bpageno = (1:5155)
bdbid = 5                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 4049                        bstat = 0x9
blog = 0x5adb215a                   bnext = 0x0000000000000000          

PAGE HEADER:


Page @0x000000015BABC000

m_pageId = (1:5155)                 m_headerVersion = 1                 m_type = 2
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x20
m_objId (AllocUnitId.idObj) = 331   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594059620352                                
Metadata: PartitionId = 72057594052804608                                Metadata: IndexId = 2
Metadata: ObjectId = 1973582069     m_prevPage = (0:0)                  m_nextPage = (1:5157)
pminlen = 5                         m_slotCnt = 250                     m_freeCnt = 16
m_freeData = 7676                   m_reservedCnt = 0                   m_lsn = (42:12960:22)
m_xactReserved = 0                  m_xdesId = (0:2280)                 m_ghostRecCnt = 0
m_tornBits = 424828296              DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                        DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED           

Open in new window


I suggest waiting for the full checkDB to finish up as it may flag other corruption that helps give a clearer picture of what's going on.  Have your backups been successfully completing (with verification) ?
0
Deepak ChauhanSQL Server DBACommented:
In the mean time you can check suspect page table in MSDB database. There will be all currupted page entries.

select * from msdb.dbo.suspect_pages
where database_id =24
0
stlhostAuthor Commented:
I had to cancel it because it was causing slow down for production people I will check it at night. Mean while I will accept the answers, thanks for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.