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 |
|
benh
Starting Member
2 Posts |
 Posted - 2009-05-12 : 13:30:11
|
I was able to follow one of Paul Randal's very helpful blog posts to resurrect a corrupt database. In the end, I used DBCC CHECKDB (MealsPlusDB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS; This allowed me to get the database back up and running. I'm just wondering if it is possible to know whether we lost data based on the output of the DBCC command I used.Repair: The Nonclustered index successfully rebuilt for the object "dbo.TransactionHistory, IX_ForVoids" in database "MealsPlusDB".Repair: The page (1:14610) has been deallocated from object ID 882102183, index ID 4, partition ID 72057594047496192, alloc unit ID 72057594052214784 (type In-row data).Msg 8945, Level 16, State 1, Line 1Table error: Object ID 882102183, index ID 4 will be rebuilt. The error has been repaired.Msg 8928, Level 16, State 1, Line 1Object ID 882102183, index ID 4, partition ID 72057594047496192, alloc unit ID 72057594052214784 (type In-row data): Page (1:14610) could not be processed. See other errors for details. The error has been repaired.Msg 8939, Level 16, State 98, Line 1Table error: Object ID 882102183, index ID 4, partition ID 72057594047496192, alloc unit ID 72057594052214784 (type In-row data), page (1:14610). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12584969 and -6. The error has been repaired.Msg 8976, Level 16, State 1, Line 1Table error: Object ID 882102183, index ID 4, partition ID 72057594047496192, alloc unit ID 72057594052214784 (type In-row data). Page (1:14610) was not seen in the scan although its parent (1:80231) and previous (1:202930) refer to it. Check any previous errors. The error has been repaired.Msg 8978, Level 16, State 1, Line 1Table error: Object ID 882102183, index ID 4, partition ID 72057594047496192, alloc unit ID 72057594052214784 (type In-row data). Page (1:78745) is missing a reference from previous page (1:14610). Possible chain linkage problem. The error has been repaired.CHECKDB found 0 allocation errors and 4 consistency errors in table 'TransactionHistory' (object ID 882102183).CHECKDB fixed 0 allocation errors and 4 consistency errors in table 'TransactionHistory' (object ID 882102183).CHECKDB found 0 allocation errors and 4 consistency errors in database 'MealsPlusDB'.CHECKDB fixed 0 allocation errors and 4 consistency errors in database 'MealsPlusDB'. Thanks for your input. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-05-12 : 14:19:34
|
| The initial checkDB that you did, did it specify that REPAIR_ALLOW_DATA_LOSS was required, or did it specify that REPAIR_REBUILD was required?--Gail ShawSQL Server MVP |
 |
|
|
benh
Starting Member
2 Posts |
Posted - 2009-05-12 : 14:39:27
|
| Gail, I'm afraid I don't have the information you're asking for. Thinking back over the steps I took earlier this morning, I don't believe I ran a CHECKDB absent the REPAIR_ALLOW_DATA_LOSS option after getting the database into emergency mode. I tried various DBCC commands on the suspect database before figuring out emergency mode, but they all failed with various errors. In hindsight, I now realize I probably had more options available to me once the db was running in emergency mode. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-05-12 : 14:54:13
|
| You very likely did. From what I can see, all the errors are in index 4 - a nonclustered index. If the DB was suspect (I assume, since if it wasn't you wouldn't have needed emergency mode), SQL probably hit the corruption during restart recovery. Since all the errors appear to have been in a NC, dropping and recreating the NC should have been sufficient. If that is the full and complete output of checkDB's repair, then you lost no data as all the repairs were to a NC index.--Gail ShawSQL Server MVP |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2009-05-14 : 10:06:58
|
| Don't forget to figure out what caused the IO subsystem to corrupt the NC index page in the first place and fix it. Next time it happens you may not be so luck with what gets whacked in the database.Paul S. Randal,Managing Director, SQLskills.com Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandalSQL MVP, Microsoft RD, Contributing Editor of TechNet MagazineAuthor of SQL 2005 DBCC CHECKDB/repair code |
|
|
|
scoots987
Starting Member
6 Posts |
Posted - 2009-06-19 : 15:48:01
|
| Along the lines of the use of dbcc checkdb what do we do first? I tried this command on its own and I don't find any errors. I need to find what is slowing down my database to a crawl but I am not sure what tool to use.The problem:For two months we had a custom app running without issue. This custom app has been in use for 4 years, btw. Then started getting reports that all functions of this app were slowing down. To a crawl. Then created a new database and started using it and everything was fine for about 3 weeks. After trying to run dbcc checkdb and not finding anything I decided to create yet another database and the app has been running for 4 days until today the users reported that is it slowing down again.How do I determine what is causing this problem now that it didn't happen the first 2 months? I created the databases exactly the same way. I know we can talk indexes and efficiencies but I shouldn't have to do this since it was fine with the first database for 2 months. My guess is that something has been turned on that is slowing this down, not at first.I did try running the "profiler" but that tool really affects performance while the databases are in use.Got any suggestions?SQL Server 2005 standardVB 6 custom applicationThat has run for 4 years without issue on same hardware. We did add more memory in hopes that this will help, but it didn't.Thanks in advance!!!! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-06-20 : 05:36:06
|
| I'd say go look at your indexes and your queries. Data volumes grow and if the indexes aren't maintained properly or are becoming less efficient it's quite possible that the DB is slowing down. After all, everything's fast on 10 rows but that's not the case on 10 million.Also make sure that you're maintaining the indexes as necessary (rebuild/reorganise)Take a look at these two articles for a suggestion on how to go about evaluating current performance.http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/Database corruption's not going to cause slow performance degradation. It causes high severity, fatal errors.--Gail ShawSQL Server MVP |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2009-06-23 : 10:41:16
|
| Gail - you're wrong I'm afraid. I/O corruption can indeed cause horrible performance problems. SQL in 2005 will retry reads 4 times before declaring a failure. If each of those reads goes to the the max timeout before failing (or worse, completing so it just looks like a perf problem) then you'll know nothing about corruption. I've seen this many times - no corruption errors but poor performance and it's the I/O subsystem corruption that's the culprit.If the database was suspect then EMERGENCY mode is the only way to allow repair to run, and then REPAIR_ALLOW_DATA_LOSS is the only repair option allowed. REPAIR_REBUILD throws an error in EMERGENCY mode.ThanksPaul S. Randal,Managing Director, SQLskills.com Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandalSQL MVP, Microsoft RD, Contributing Editor of TechNet MagazineAuthor of SQL 2005 DBCC CHECKDB/repair code |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-06-23 : 16:44:31
|
| never mind..... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-06-23 : 17:03:17
|
| Would CheckDB indicate that an 825 occurred, or would it just go into the error log as in normal operation?--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Interpreting DBCC CHECKDB output