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.
| 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?GregMsg 8928, Level 16, State 1, Line 1Object 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 1Table 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. |
 |
|
|
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 |
|
|
|
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. |
|
|
|
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 |
|
|
|
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;GOALTER DATABASE {db name}SET SINGLE_USERWITH ROLLBACK IMMEDIATE;GOUse {db name};DBCC CHECKDB ({db name}, REPAIR_ALLOW_DATA_LOSS)dbcc checktable ({table name})[/CODE]Thanks once again for the help.Greg |
|
|
|
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 |
|
|
|
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/ |
|
|
|
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 |
|
|
|
|
|
|
|
|
Restored from Backup and still corrupted