January 10, 2015 at 3:16 am
So, one of my queries produced this result tonight:
Server: Msg 824, Level 24, State 2, Line 13
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x2b1ad905; actual: 0xcceeca51). It occurred during a read of page (1:1542146) in database ID 10 at offset 0x000002f1004000 in file 'C:\Install\Microsoft\SQLServer\MSSQL.1\MSSQL\DATA\MarketSmith.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.
There doesn't seem to be any additional info in ERRORLOG.
DBCC CHECKDB ('MarketSmith') WITH NO_Infomsgs, ALL_ERRORMSGS produces:
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2815072, index ID 1, partition ID 72057594275823616, alloc unit ID 72057594264354816 (type In-row data): Page (1:1542146) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 2815072, index ID 1, partition ID 72057594275823616, alloc unit ID 72057594264354816 (type In-row data), page (1:1542146). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 2815072, index ID 1, partition ID 72057594275823616, alloc unit ID 72057594264354816 (type In-row data). Page (1:1542146) was not seen in the scan although its parent (1:1702287) and previous (1:1542145) refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 2815072, index ID 1, partition ID 72057594275823616, alloc unit ID 72057594264354816 (type In-row data). Page (1:1542147) is missing a reference from previous page (1:1542146). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'MSImport' (object ID 2815072).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'MarketSmith'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MarketSmith).
So, I've poked around and it looks like I am pretty "lucky". It looks like I have just one corrupt page. I have dumped the page (DBCC PAGE), and the data looks pretty normal. I have done an eyeball scan and it all looks reasonable. I have figured out the records that are contained on this page so I could do a repair_allow_data_loss and re-create the records from the source data. A PITA, but doable. I could also try a hex overwrite of that page from a backup - I'd have to figure out how to find the offset into the file, etc, etc. Any thoughts?
My other, almost bigger, question is how did this page get corrupted? The page that is corrupted was written Apr 6, 2011 and hasn't been updated since. Like I said, I gave it a quick look and it doesn't really look corrupted. I am running SQL in a VM on a SSD that is less than 2 months old and the drive is not showing any errors and the quick self-test passed. I run this same query every night, so it seems like this corruption happened in the last 24 hours.
The only unusual thing I did tonight is I tried to run SQL's BI to import some data while I had the SQL Service turned off. Of course, that didn't work, so I fired up the SQL Service, tried the import again, and it worked. It seems hard to believe this would cause this problem, but I don't trust coincidences either.
Thanks, -randy
January 10, 2015 at 12:21 pm
First of all welcome to the forum, next some quick questions, have you checked the SMART for the SSD? Is this the full and unedited/altered output of DBCC?? How did you transfer the image to the new SSD? Is restoring from a backup an option? Is the table in question critical?
Suggest you don't attempt to edit the page directly unless there are no other options. Not certain how "lucky" you are, somewhat depends on the your answers 😉
😎
January 10, 2015 at 2:26 pm
- have you checked the SMART for the SSD?
Yes, no failures are reported on the attributes page and the short offline test reported no errors. I ran the extended offline test this morning and it also does report any errors.
- Is this the full and unedited/altered output of DBCC??
Yes (I am not sure why you ask - did you expect to see something else?)
- How did you transfer the image to the new SSD?
I upgraded from a 255GB SDD to a 512GB SSD. I installed a newer version of Mint and then copied the VM containing SQL Server and the databases from the old drive to the new drive. That was at the end of Nov.
- Is restoring from a backup an option?
Possibly. It would be a month+ old and then I would have re-apply data.
- Is the table in question critical?
Yes, it is the central table in the DB.
January 10, 2015 at 3:18 pm
OK, this is bizarre. Now it is completely happy. All DBs have no errors. I did a diff of the page dump from last night compared to now (shown below). I have done nothing to the DB. I have not bounced or suspended anything (i.e. SQL Server, the VM, or the host). I have only ran the SMART tests and have been issuing DBCC-type commands (definitely no updates). I did a backup and still not seeing any errors. Any clues what is going on? Thanks, -randy
9c9
< BUF @0x03F38BC0
---
> BUF @0x03E8BCA0
11,13c11,13
< bpage = 0x21B24000 bhash = 0x00000000 bpageno = (1:1542146)
< bdbid = 10 breferences = 1 bcputicks = 0
< bsampleCount = 0 bUse1 = 52581 bstat = 0xc00809
---
> bpage = 0x1E17E000 bhash = 0x00000000 bpageno = (1:1542146)
> bdbid = 10 breferences = 3 bcputicks = 510
> bsampleCount = 1 bUse1 = 32241 bstat = 0xc00009
19c19
< Page @0x21B24000
---
> Page @0x1E17E000
35c35
< PFS (1:1536720) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:1533702) = CHANGED
---
> PFS (1:1536720) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:1533702) = NOT CHANGED
108,109c108,109
< 00000400: 9b9304c3 f094348b dbe0ec45 10d89ad9 å??.+=?4?¦a8E.+?+
< 00000410: 00000000 00000002 00000000 00000000 å................
---
> 00000400: 00000000 0000f03f 33333333 33332240 å......=?333333"@
> 00000410: 00000000 00000000 00000000 00000000 å................
715c715
< # New Highs in Group = -1.096596874981535e-254
---
> # New Highs in Group = 1.000000000000000e+000
719c719
< % New Highs in Group = -4.436346327006989e+123
---
> % New Highs in Group = 9.100000000000000e+000
723c723
< # New Lows in Group = 4.778309726736481e-299
---
> # New Lows in Group = 0.000000000000000e+000
January 11, 2015 at 4:07 pm
I ran a memory tester and it looks like I have some issues with my memory. Relatively new high-end laptop w/ brand-name memory, so I am little surprised...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply