Newbie- Need to add database and run DBCC CHECKDB

[Zych]

Hello All,

How can I fix a corrupted database? From what I can see I should run dbcc checkdb on it. However I am very new to MS-SQL. I have gone into Enterprise Manager but I do not see that database. I have tried the attach command but it fails do to an error in which I think is do to the corruption. (It says Error 823:I/O error (bad page ID)detected ...) How do I run this against the database? Do I need to attach to it first? If so how can I attach to it if it is corrupted?

Thanks,

Zych

 

[mrdenny]

Your enterprise manager is probably just not rereshed. This isn't what you need to be worried about at the moment.

First lets see if the database is attached to the SQL Server.
In Query Analyser run this code.

select * from sysdatabases where name = '{database_name}'

If that returns 0 records then the database isn't attached at all. If it returns a record then the database is attached to SQL and you can run the dbcc command

dbcc checkdb ({database_name})

This will tell you what your problem is.

If the database isn't attached, then you probably have some sort of disk problem. Please post the entire error message as well as exactally when it comes up.

How did you deturmin that the database was corrupt? What else has been tried to fix it?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Zych]

Thanks for the response. I will give this a try.

Here is the whole story behind the corruption. I have a client who runs a program called ServiceCEO for his company. This program uses desktop SQL to run. His drive started to report problems through SMART saying he better back it up soon because it was failing. I did this but the one thing that did get corrupted was the database. It would not allow anybody to attach to it through the ServiceCEO program. He had a backup from 4/2/2005 but he has a lot of customers since then. The ldf file is about 2.5GB in size and I think it was created about two years ago. I am hoping I can pull the last two weeks of information from it. At least the customer names and info.

I also know nothing (or next to it) on MS-SQL. I do use MySQL on a regular bases but they are two very different creatures. The hard drive in my system is fine as far as I can tell. I just loaded MS-SQL 2000 yesterday to work on this problem. Under Enterpise Manager I can browse to the databases and I do not see it listed, though I see others like Northwind. (Which I would imagine is a demo database like that in MS Access.)

If I right click on databases and choose All Tasks -> Attach Databases ... It opens up a window. In this window I select the database ServiceWorks.mdf and it returns the following error:

---------------------------
Microsoft SQL-DMO (ODBC SQLState: HY000)
---------------------------
Error 823: I/O error (bad page ID) detected during read at offset 0x000000009d2000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ServiceWorks.mdf'.
---------------------------
OK
---------------------------

From the research on Google that I have done it seems this is caused usually hardware failure, which is also true in this case. But now that I have moved it to a good system I would like to try and repair it. Everything that I see says to run DBCC checkdb.

I have just run your first command and it came back with 0 rows. How can you run checkdb if you can not attach to the file? Is there a way to force this even with errors?

Thanks for your help,

Zych

 

[mrdenny]

It looks like the mdf became corrupt on the old drive. You can't run dbcc checkdb until the database is attached to the SQL Service.

If his disk is still alive backup the database and restore it to another system using the backup database command. This should work ok. You may need to dbcc checkdb on it on his machine first.

I think there is a way to attach a corrupt database to the SQL Server, but I'll have to find the commands if there is a way.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Zych]

Thanks mrdenny,

I have followed some of the instructions found here:

http://groups-beta.google.com/group...c.sqlserver.server&start=450#7f1450337acb3fb3

I am going to try and run DBCC checkdb if I can figure out exactly how to do this. We will see what happens.

Thanks,

Zych

 

[mrdenny]

Run the dbcc command in Query Analyzer.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Zych]

Thanks Denny,

I tried that but I get a different error. I do not think SQL is seeing it right. If I try and open it there is no tables or anything. This may be do to the creating and copying part (therefore not registered correctly). I'm not sure. It will not do the checkdb giving some error. I am about to give up on it.

here is the error for those wanting to know:

Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:749) with latch type SH. sysobjects failed.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I have no idea what the sysobjects is or how it relates to my database.

Thanks again for your help,

Zych

 

[mrdenny]

sysobjects is a table that holds the meta data for all the tables, views, procedures, indexes, etc.

Sounds like the database file it self it pretty damaged.

I'd recommened a couple of approaches. You can try backing up the database with the BACKUP DATABASE command and try restroing it to another database name.

You can also try to repair the database with the highest repair level.

DBCC CHECKDB ('{database_name}', REPAIR_ALLOW_DATA_LOSS)
WITH ALL_ERRORMSGS

That may try to repair the problem, and give you some more info about the problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Zych]

Thanks mrdenny,

I have already tried the dbcc checkdb with the allow data loss. I did not try it with all_errormsgs though. I may give it a try and see what happens.

-Zych

 

[mrdenny]

Cool, hopefully it will give you some usefull info.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)