0

Reformulated the Question

I have a .BAT script that runs the DBCC CheckDB and repairs the database if needed. The result of the DBCC CheckDB is sent to OSQL through the -b flag and the .BAT determines if there's an error using "errorlevel 1"

The problem I'm seeing is that when there's an error that is repaired, the result returned to OSQL still shows as if there were errors... but in reality it was repaired and there isn't any errors left.

Is there a way that the DBCC checkdb would return results to the .bat based on the end result (If there are still errors or not)? maybe with a different errorlevel, a different OSQL flag or maybe using a different method?

In summary, My script returns an error message even though the dbcc checkdb fixed the errors.

More info

Below is a simplified version (simplified as much as I can)... this script usually runs with parameters (10 of them)...

    rem p8=REPAIR  --> Parameter REPAIR when the user has agreed for possible data loss
    set p8=BASIC

    :WINauth
    if %p8%==REPAIR goto WINRepair

    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "DBCC CHECKDB(DemoCorruptMetadata2008R2)" -b -o c:\DBCC_CHECKDB.log
    if errorlevel 1 goto WINDBCheck
    goto end

    :WINDBCheck
    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "ALTER DATABASE DemoCorruptMetadata2008R2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE" -b -o c:\DBCC_CHECKDB.log
    if errorlevel 1 goto GeneralError
    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "DBCC CHECKDB(DemoCorruptMetadata2008R2,REPAIR_REBUILD)" -b -o c:\DBCC_CHECKDB.log
    if errorlevel 1 goto WINDBCheckError
    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "ALTER DATABASE DemoCorruptMetadata2008R2 SET MULTI_USER WITH ROLLBACK IMMEDIATE" 
    goto end

    :WINDBCheckError
    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "ALTER DATABASE DemoCorruptMetadata2008R2 SET MULTI_USER WITH ROLLBACK IMMEDIATE" 
    goto DBCheckerror

    :WINRepair
    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "ALTER DATABASE DemoCorruptMetadata2008R2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE" -b -o c:\DBCC_CHECKDB.log
    if errorlevel 1 goto GeneralError
    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "DBCC CHECKDB(DemoCorruptMetadata2008R2,REPAIR_ALLOW_DATA_LOSS)" -b -o c:\DBCC_CHECKDB.log
    if errorlevel 1 goto WINDBRepairError
    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "ALTER DATABASE DemoCorruptMetadata2008R2 SET MULTI_USER WITH ROLLBACK IMMEDIATE" 
    goto end

    :WINDBRepairError
    "C:\Program Files\Microsoft SQL Server\120\Tools\Binn\OSQL.exe" -S (LOCAL)\SQLEXPRESS -E -d DemoCorruptMetadata2008R2 -l 10 -Q "ALTER DATABASE DemoCorruptMetadata2008R2 SET MULTI_USER WITH ROLLBACK IMMEDIATE" 
    goto DBRepairerror


    rem +++++ Exit Routine +++++

    :GeneralError
    echo Cannot Put the database in Single user mode. > c:\DBCC_CHECKDB.log
    goto end

    :DBCheckerror
    echo We suggest to use the "Repair" Option > c:\DBCC_CHECKDB.log
    goto end

    :DBRepairerror
    echo CHECKDB with higher level of repair failed. > c:\DBCC_CHECKDB.log
c:\DBCC_CHECKDB.log
    goto end

    :end
  • CHECKDB, by itself doesn't just repair errors, it identifies them. Are you just throwing the old REPAIR_ALLOW_DATA_LOSS at it after it finds issues? If you are, please stop, you may be able to fix the issue without losing data. What's the exact error DBCC is giving you when it finds corruption? – Kris Gruttemeyer May 20 '16 at 15:27
  • @Kris Gruttemeyer It tries DBCC CheckDB to see if all is ok. If it finds errors we run the DBCC checkdb with Repair option. If there are still errors we ask the user the consent to run the DBCC With REPAIR_ALLOW_DATALOSS option. – JohnG May 20 '16 at 15:34
  • What is the exact error it throws when it finds corruption. REPAIR options are always the absolute last resort. From BOL: 'Use the REPAIR options only as a last resort.'. If the corruption is in a nonclustered index, all you would have to do is drop and recreate it. Can you update the question to show what exact corruption it's finding? We may be able to fix the problem at it's source instead of just band-aiding it. – Kris Gruttemeyer May 20 '16 at 15:38
  • @Kris Gruttemeyer, It's a general script that should cover all errors. Most of the the time the REPAIR_REBUILD option is able to fix the majority of the problems. I used the Corruption Demo Database from Paul Randal to do testing and it seems to be working fine but when an error is automatically fixed (such as the rebuild of an index), It's still thrown as an error in the batch file as errorlevel 1. I will add parts of the batch file in my question. – JohnG May 20 '16 at 15:46
  • @Kris Gruttemeyer, I've added more info. – JohnG May 20 '16 at 17:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.