CODEWITHSUNDEEP
sql-servervb.netdbcc
Aousaf Rashid
Aousaf Rashid

Reputation: 5738

DBCC CheckDb-any ways to detect errors vb.net?

I am using the below code to check if my database has any issues/requires troubleshooting:

Dim cmd As New SqlCommand("DBCC CHECKDB (offpoDb) WITH TABLERESULTS", con)
Dim reader As SqlDataReader = cmd.ExecuteReader
executing.Content = "Checking datatabse for errors"
executing.Margin = New Thickness(243, 111, 0, 0)
While reader.Read
    strBuilder.AppendLine(CStr(reader("MessageText")))
End While
reader.Close()
MessageBox.Show(strBuilder.ToString)

Now, the DBCC CHECKDB command might result in something like this :

CHECKDB found 0 allocation errors and 15 consistency errors in database 'mydb

Which can be fixed the following SQL query :

ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER WITH ROLLBACK 
IMMEDIATE;
BEGIN TRANSACTION;
DBCC CHECKDB ('AdventureWorks2008R2', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE AdventureWorks2008R2 SET MULTI_USER;

But before executing the following query through my application, is there a way to know if DBCC CHECKDB has really returned any errors at all ? Because without any errors, repairing the database will be useless...

Any help ?

AN IDEA THAT CROSSED MY MIND

I was thinking of getting the string from strBuilder to a textbox.The textbox will be checked for the availability of the following line CHECKDB found 0 allocation errors and 15 consistency errors in database..

But this is still not possible because that line can be different from time to time.E.g.

CHECKDB found 0 allocation errors and 15 consistency errors

CHECKDB found 1 allocation errors and 14 consistency errors

Any better ideas ?

Upvotes: 0

Views: 370

Answers (1)

MatSnow
MatSnow

Reputation: 7517

There are multiple possibilities:

1.
Add NO_INFOMSGS option to the command.
Like this, no records will be returned if no errors are found.

DBCC CHECKDB (dname) WITH TABLERESULTS, NO_INFOMSGS

2.
Read the value(s) of column Level. If one is higher than 10, an error occured. (Reference)
A list of the DBCC-errors you can get with:

SELECT * FROM sys.sysmessages 
WHERE description LIKE '%checkdb%' AND msglangid = 1033 AND severity > 10

3.
Check the result-string for a number higher than 0.

For Each c As Char In "CHECKDB found 0 allocation errors and 15 consistency errors"
    If (Char.IsNumber(c)) AndAlso Integer.Parse(c) > 0 Then
        'Errors occured
    End If
Next

Upvotes: 3

Related Questions