Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Restored from Backup and still corrupted

Author  Topic 

GregDDDD
Posting Yak Master

120 Posts

Posted - 2013-02-12 : 19:53:52
We had a bit of a melt down on Friday with one of the databases. The server SS 2005 is running on crashed and after restarting, the db in questions was Suspect. I followed some instructions to move it to Emergency and attempt a repair but about 20 minutes in I saw an error in red in the query window just before the server went down again. I didn't get a good look at the error. No fun.

We brought the server back up and restored from a 12:00 noon back up, so we only lost a few hours of data. We then found we were in need of a firmware update on the hd controller, so we did that today. After running DBCC CHECKDB({dbname}) today I found the errors below.

We are lucky in a way that the table in question is normally pruned every night of data that is more than 30 days old. Due to some reporting requirements for a grant we have been holding on to a lot more data in that table, but I am prepared to lose data from this table. The grant ends in two weeks and we will go back to pruning data.

My thought was to run CHECKDB repair_allow_data_loss and just let the chips fall were they may, if I can only expect to lose data from that one table with this method. This table is on the many side of a one to many relationship with another table, so I won't orphan any records in other tables.

The real question is, how bad is this, both in data loss, but also in how fast I should attempt the repair. Can I wait two weeks to try one more round of reporting. One thought was to fix this now and then try and report off a back-up for pre-repair dates and off the live database for post-repair dates.

Any thoughts?

Greg
Msg 8928, Level 16, State 1, Line 1
Object ID 117575457, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594043039744 (type LOB data): Page (1:6426225) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 117575457, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594043039744 (type LOB data), page (1:6426225). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 113379337 and -4.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-02-13 : 07:01:03
Try a table scan of that table to see where the corruption is.
You might then be able to access data around the corruption using indexes. Presumably you have an index on the date to do the pruning so you should be able to access data that you need if it is available.

Then copy data that you can access into another table - try dropping the original and running the checks.
If that doesn't give you anything usable you probably don't have any option other than a previous backup or a repair.

Do you run frequent checks on the database - I guess not. I usually run them with the full backup schedule.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

GregDDDD
Posting Yak Master

120 Posts

Posted - 2013-02-13 : 11:40:46
Thanks. I misspoke a little in the initial message. We had to restore from Thursday's back-up because the Friday noon back up failed. When we started having problems Friday afternoon, this is when we discovered that failure. I don't manage back-ups, but Symantic reported a failure running DBCC when the back up failed - 'The request could not be preformed because of an I/O failure'. When we contacted Dell we found out about the firmware issue with the hd controller. I thought Thursday's back up would be good, but that is the one we are live with now which has the corrupted data on the one table.

I would rather not restore from an older back up if I don't have to. Like I said, loosing data from this table is not the end of the world. Loosing data from other tables would be less desirable. More than 90% of the data gets in to the db via automated processes from outside our building. Those sources will queue the data if we go off-line and start sending again when we come back up. So even though we were down for 3 or 4 hours, we may have actually lost no data. If I restore from a back up from last week before the corruption occurred we would loose data.

This table was created by the vendor. There are 8 indexes on the table. Six are non-unique, non-clustered, and two are unique, clustered. When I do a select top 1 * and order by 2 fields that make up one of the indexes - one being created_date - the query returned 1 row with no errors. I ran a number of other queries on the table with no errors.

Greg
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-02-13 : 12:09:08
Then try following the process I suggested above.
If the data is just inserted by date then maybe you can also get historic data from an older backup if you aren't able to access enough on this version.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

GregDDDD
Posting Yak Master

120 Posts

Posted - 2013-02-13 : 12:30:10
Sounds good. If I do attempt a repair with CHECKDB (dbname, repair_allow_data_loss) can this be done on a live db or will it be taken off-line. I've never used this feature before.

I just ran this query

SELECT convert(varchar(2), [DATE_CREATED], 101) + right(convert(varchar(10), [DATE_CREATED], 101), 4), COUNT(SEQUENCE_ID)
FROM [dbo].[MESSAGE]
group by convert(varchar(2), [DATE_CREATED], 101) + right(convert(varchar(10), [DATE_CREATED], 101), 4)

To get a count of records by month. It ran fine. I thought maybe I would have issues if the data was corrupted.

Greg
Go to Top of Page

GregDDDD
Posting Yak Master

120 Posts

Posted - 2013-02-13 : 17:56:48
In case anyone stumbles on to this thread, I did need to put the db into single user mode to make the repair. I tried this first on a back up and I will make the real repairs tomorrow morning.

Before I started I ran the query above to get row counts, grouped by month, and then ran the query after the repair. There was data loss from the current month, but it was less than one half of one percent. I would have lost more had I gone back to an uncorrupted db from last week, so I am happy.

[CODE]
USE master;
GO
ALTER DATABASE {db name}
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
Use {db name};
DBCC CHECKDB ({db name}, REPAIR_ALLOW_DATA_LOSS)

dbcc checktable ({table name})
[/CODE]


Thanks once again for the help.

Greg
Go to Top of Page

GregDDDD
Posting Yak Master

120 Posts

Posted - 2013-02-13 : 18:02:14
I take that back, we gained rows! I wasn't expecting that.

Greg
Go to Top of Page

jayismyson
Starting Member

4 Posts

Posted - 2013-03-20 : 02:26:46
one being created_date - the query returned 1 row with no errors. I ran a number of other queries on the table with no errors.


_________________
http://www.mmoggg.de/
http://www.saferunescapegold.com/
Go to Top of Page

davegeeit
Starting Member

11 Posts

Posted - 2013-03-21 : 02:26:58
In order to resolve such issues of SQL Server in an efficient manner, RecoveryFIX for SQL Database Recovery is one such proficient third party tool. By using this tool, you can easily recover your lost, corrupt or inaccessible SQL database files from all the versions of the corrupt Microsoft SQL server.

Thanks
Go to Top of Page
   

- Advertisement -