Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Have a ssvr 2008 db, about 10gb. About 50 tables, 5 or so are frequent read/write, the rest are weekly write (append only), occasional read.

The server came up and down a couple times over the past week due to Sandy (gracefully, from what I can tell from the logs, it's on a UPS). Today, it was noticed a lot of records in a single table (one of the weekly write ones) were missing (everything from september forward to the first write after the last power issue). The newest records added after the last restart are fine. That's problem 1.

The arguably weirder problem is that the surviving records before september are mostly correct, except for the data in two columns (nvarchar(50) and nvarchar(100)), which has been uniformly replaced with dates (YYYY-MM-DD) and bytes (ranging from 0 to 192). The dates and bytes don't have much relation I can see to the data that should be there or the other columns in that table (the dates are all "sensible", though, between 2012-02-27 and 2012-09-09).

I'm fetching backups for before and after each outage (simple recovery, hourly full backups) so I should be able to recover, but I'm a bit disturbed.

Main concerns:

  • How'd this happen without any events in the log or CHECKDB errors?
  • What could cause this in general?
  • Is there any way I can determine if other tables have been similarly corrupted in ways that may not have been as obvious as the first?
share|improve this question
1  
Are page checksums turned on? If not, I would highly recommend it. –  Jon Seigel Nov 8 '12 at 17:40
    
Good call, it was on torn page detection, I bumped it up to checksum. Can a hdd-level issue cause such a broad and "well-behaved" problem, without checksums on? –  Pastymage Nov 8 '12 at 19:08
1  
First of all, I should mention that turning on page checksums doesn't automatically apply to all pages in the database -- the new setting only applies to pages that are modified and then flushed to disk, so the existing DB will only eventually have it enabled on all pages. To answer your question, yes, I think it's possible. It sounds like possibly a metadata page was corrupted. Torn page detection is only capable of catching torn writes, it doesn't really "incorporate" the contents of the page like checksums do. –  Jon Seigel Nov 8 '12 at 22:42
    
@Jon Yeah, I read about that, I'll do a full re-index over the weekend so it actually calculates all the checksums. I'm recovered from the backups, so we'll see what happens. This is a fairly new server, I really hope we're not losing a drive already... –  Pastymage Nov 8 '12 at 22:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.