DBCC CHECKDB Won't run on corrupted database SQL2K

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

DBCC CHECKDB Won't run on corrupted database SQL2K

Author

Topic

chris_cross
Starting Member

6 Posts

Posted - 2007-03-29 : 17:05:14

Let me start off by saying that under normal circumstance I would just restore from the last good backup. However in this case it appears as though the last good backup was sometime last August ... arg! After much yelling at the person responsible I've been attempting to get my blood pressure below 200 and see what data is recoverable.

First off, this was a RAID5 system that failed 1 drive. Secondly, before we got someone in there to replace said drive it failed a second drive and the system went down. We managed to massage the system back online but it appears that there is some corruption as a result which is no surprise.

I've done DB repairs in the past and it hasn't been too bad, but this time it is looking a little gnarly.

I've kicked everyone off the server and tried starting SQLServer several different ways.

I tried starting the service normally and then flagging the bad DB into single user mode with "ALTER DATABASE foo SET SINGLE_USER". I then did a select * from sysdatabases to make sure it took, which it did. I also tried starting the whole SQLServer in single user mode from the command line, "SQLServr -m".

I can run "DBCC CHECKDB('foo')" and I get a long ugly list of allocation errors. I posted it to a link as the 1349 lines returned is a little long:
http://chrisnet.net/sqlbad/dbcc_checkdb.txt

But when I attempt to bite the bullet and destroy data in an attempt to put things back together with:
"DBCC CHECKDB('foo', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS"
I get:
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

But yet the database is in single user mode, according to everything I check check on. Is this just SQL's way of telling me the corruption is too severe to be repaired? No output is displayed in the shell cmd window like it is for a successful DBCC either (when running sqlservr -m).

Ideas? Or do I put a bullet through it's brain?

-Chris

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-03-29 : 22:02:20

Hi Chris,

Doesn't look *too* bad - looks like there's a few pages that are corrupt that are causing most of the errors because of missing links from the corrupt pages.

My guess is that there's another database state set for that database - can you post the results from

select * from sysdatabases where name='yourdb'

I don't see anything in the list of errors that would cause problems for repair (although its 10pm here in Florida and I've been lecturing since 9am with Kimberly at SQL Connections so maybe I missed something.)

Note: I may not reply until Saturday morning PST as I'm flying from Florida to Redmond at 6am EST Friday and connections are looking dodgy...

Thanks

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

chris_cross
Starting Member

6 Posts

Posted - 2007-03-29 : 22:36:30

Thank you for the reply, I know it's tough when you're on the road.

I'm an MVP for DirectShow, so I'm a little out of my usual element, but I've been working with MSSQL intensely since the 6.5 days. I prefer the programming side of the fence, but I'm comfortable with the nitty gritty details.

I tried setting the status directly via an adhoc query on sysdatabases, which was successful for getting into emergency mode (32768). Setting single user mode did add 4096 to this, but after a restart of sql it has reset itself.

Name Status sid mode status status2 crdate
Foo 7 0x0105000000000005150000004D64492E0B75D97643170A32F4010000 0 4231169 1090519040 2006-08-23

reserved cat cmptlev filename version
1900-01-01 00:00:00.000 0 80 E:\SQLData\foo_data.mdf 539

So converting that to hex I get 0x409001 which would appear that emergency mode and single user mode are both still on but with two 'extra bits' that I don't know about.

Another factoid, the transaction log is 700MB and the DB file is 250MB (but not fully populated). Are there potential memory issues here? I don't think so as I've processed transaction logs that are several GB's before without issue, but just putting it out there.

I tried to restore the original backup from last August to another name to see how much it contained, but at the end of the restore I'm getting an error. This backup should be good, I will test another server to be sure.
Processed 2800 pages for database 'foobak', file 'foo' on file 1.
Processed 1 pages for database 'foobak', file 'foo_log' on file 1.
Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 2 in database 'foobak'.

SQLServer is v2000 SP4 (2039).

BTW the problem server is in Florida if you want to swing by ... <grin> I'm 1500 miles away using remote access.

-Chris

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-03-29 : 23:15:03

Take the database out of emergency mode (but leave it in single_user mode) and repair should run fine. Remember that emergency mode is really undocumented in SQL 2000 which is why you're not getting a totally clear error message from checkdb.

I'll tackle the other stuff later. Please let me know how you get on.

Cheers.

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

chris_cross
Starting Member

6 Posts

Posted - 2007-03-30 : 09:23:27

Ah, yes. Thanks for reminding me, that bites me everytime on 2000.

But I had tried that previously as well. That generated a new error:

Server: Msg 945, Level 14, State 2, Line 1
Database 'foo' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

I check the log and there are no details. On startup it does post this error however:
2007-03-30 07:42:48.49 spid11 Error: 9004, Severity: 21, State: 10
2007-03-30 07:42:48.49 spid11 An error occurred while processing the log for database 'foo'..

I will take a copy of the DB and attach an empty log file to see if recovery will run on that.

Thanks again for you help. I flew from Redmond to Orlando 2 weeks ago, connecting via Chicago wasn't the best idea but I eventually got there. So have a good flight, and may the connection fairies be with you.

-Chris

chris_cross
Starting Member

6 Posts

Posted - 2007-04-02 : 09:15:30

I tried starting up a copy of the .mdf file with a blank .ldf to see if I could repair that but I got a similar error. I lost my VPN access and I'm working with a local admin to get it back.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-04-03 : 11:16:10

Chris, any progress on this problem?

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

chris_cross
Starting Member

6 Posts

Posted - 2007-04-03 : 11:24:50

No, at my last attempt I was still unable to access one table due to the data file corruption on the .mdf, the other tables were in fair shape. Unfortunately the site's VPN router blew up and they currently have a tech on-site fixing that.

I'll post a message when I get my connection back.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources