Check database status.

[Ken Varn]

I want to be able to check an SQLServer database to determine if it is not damaged or in need of repair. I looked into the DBCC CHECKDB SQL call to accomplish this. However, I cannot figure out how to check the status of the database after making this call in ADO.NET. I tried using a SqlDataReader, but there is nothing returned in the reader that gives me the status of the call.

Can someone make a recommendation on how I can check the status of a database using ADO.NET?


--
-----------------------------------
Ken Varn
Senior Software Engineer
Diebold Inc.

EmailID = varnk
Domain = Diebold.com
-----------------------------------

 

[Guest]

Hi Ken,

In order to get result you should use option WITH TABLERESULTS. For example

sqlcommand.CommandText = "DBCC CHECKDB (tempdb) WITH TABLERESULTS";
datareader = sqlcommand..ExecuteReader();

Then you can get result in datareader.

HTH

Elton Wang
(e-mail address removed)

 

[Ken Varn]

Thanks for the info.

This information is really buried in the SQL Server help. I was able to
find it, but I have a couple other questions.

If I call "'DBCC CHECKDB (tempdb) WITH TABLERESULTS, NO_INFOMSGS", I get no
results back if the DB checks out ok. Is it a safe bet to assume that if I
get no results, then the DB is ok, but if I do get results then the DB could
have errors and be in need of repair?

I am trying to come up with a method for providing the ability for my users
to check the database for errors and report if it is in error.

I also want to use this same call to repair the database if it is broken,
and provide feedback on the success of the repair.

I cannot find any info in the help to show how all of this could be checked
properly with result sets.

--
-----------------------------------
Ken Varn
Senior Software Engineer
Diebold Inc.

EmailID = varnk
Domain = Diebold.com
-----------------------------------

Hi Ken,

In order to get result you should use option WITH TABLERESULTS. For example

sqlcommand.CommandText = "DBCC CHECKDB (tempdb) WITH TABLERESULTS";
datareader = sqlcommand..ExecuteReader();

Then you can get result in datareader.

HTH

Elton Wang
(e-mail address removed)

not damaged or in need of repair. I looked into the DBCC CHECKDB SQL call
to accomplish this. However, I cannot figure out how to check the status of
the database after making this call in ADO.NET. I tried using a
SqlDataReader, but there is nothing returned in the reader that gives me the
status of the call.

 

[Guest]

Hi Ken,

I think it's OK to use DBCC CHECKDB (dbname) WITH TABLERESULTS, NO_INFOMSGS
to check database and report error by users.

However, in me personal opinion, it’s not good option to let user directly
perform database repairing work. Database repairing work needs to be done in
single user mode. Anyway it’s just my suggestion. And I’m not DBA role. You
can ask DBA people’s options.

HTH

Elton

 

[Ken Varn]

I am working on an embedded system that requires some administrative tools
to check the database integrity and perform repairs if necessary.

Since I don't have any corrupted databases to test with, I am not sure what
results to expect from CHECKDB if the database is in need of repair. First,
I want to first check integrity of DB and report if the database is in
error. Secondly, if repairs are performed, I want to be able to report to
the user if the repair was successful.

I don't really know what results are going to come back on a corrupt
database to know how to report it to the user.

Are there any docs on what the result fields could be when the repair is
performed?

--
-----------------------------------
Ken Varn
Senior Software Engineer
Diebold Inc.

EmailID = varnk
Domain = Diebold.com
-----------------------------------

 

[Guest]

I don't have experience in corrupted databases neither. What I can suggest is
to ask people with DBA skills, e.g. in sqlserver group
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.server&lang=en&cr=US

AFAIK, if you want to use DBCC CHECKDB to repair DB, the DB must be started
in single user mode. That is only for maintenance.

http://support.microsoft.com/default.aspx?scid=kb;en-us;264154


HTH

Elton