3

Would anyone know how I can make DBCC CheckDB produce an error? I'm testing a script and need DBCC Checkdb in the simplest mode to return an error, also while trying to repair and when trying to go into single user mode...

The simple dbcc checkdb command thant I'm using is part of a batch file and is as follows:

OSQL -S MYPC -E -d MyDB -l 10 -Q "DBCC CHECKDB(AG_DB_STORESQL)" -b -o c:\MyFolder\MSSQL_DB_CHECK.Log

If no errors are found the script ends. If errors are found, the script tries to fix them with the REPAIR_REBUILD option and if that fails the script tries to fix them with REPAIR_ALLOW_DATA_LOSS option. Obviously, before trying to Repair the script attempts to put the DB in Single user mode (which is also error trapped)

So All I'm looking for is a way to cause DBCC CheckDB to return an error.

  • 1
    Did you searched for Paul Randal script for corrupt database on SQLSkills.com – Shanky Apr 13 '16 at 17:32
  • @Shanky I've been searching the web but didn't find anything that can help me produce an error. I will search there and let you know if I find anything. – JohnG Apr 13 '16 at 18:33
  • 1
    @Shanky This is exactly what I was looking for. I was able to restore the corrupted databases from the Paul Randal article and test with them. If you add as an answer, I can select it as the right answer. – JohnG Apr 14 '16 at 12:03
3

Handy corruption demo script has been created by Paul Randal. These are really good and would help you a lot. Please browse below links

4

Would anyone know how I can make DBCC CheckDB produce an error?

Use the lethal dbcc writepage ({'dbname' | dbid}, fileid, pageid, offset, length, data [, directORbufferpool]) ON a TEST (NON-PROD) fully sandbox environment.

The purposes of DBCC WRITEPAGE are:

  • To allow automated testing of DBCC CHECKDB and repair by the SQL Server team.
  • To engineer corruptions for demos and testing.
  • To allow for last-ditch disaster recovery by manually editing a live, corrupt database.

Apart from "The Guru" Paul Randal, @SteveStedman has a good series of Database Corruption Challenge (with solution - but refer it later once you try to address it by yourself).

0

You could create a stored producedure for this purpose:

CREATE PROCEDURE CheckDBRaiseError(@db varchar(255)) AS
SET NOCOUNT ON
IF OBJECT_ID('TEMPDB..#CheckDB') IS NOT NULL DROP TABLE #CheckDB
CREATE TABLE #CheckDB
    ([Error] int, [Level] int, [State] int,  
     [MessageText] varchar(7000), [RepairLevel] int, [Status] int, 
     [DbId] int, [ObjectID] int, [IndexId] int, [PartitionId] int, 
     [AllocUnitId] int, [File] int, [Page] int, [Slot] int,  
     [RefFile] int, [RefPage] int, [RefSlot] int, [Allocation] int); 
INSERT INTO #CheckDB
EXEC ('DBCC CHECKDB('+@db+') WITH TABLERESULTS')
DECLARE @Result varchar(4000)
SELECT TOP 1 @Result = MessageText FROM #CheckDB WHERE MessageText LIKE 'CHECKDB%allocation errors%'
IF @Result NOT LIKE '%0 allocation errors and 0 con%'
  RAISERROR('DBCC Error: %s', 20, 1, @Result) WITH LOG

Then update your command line to

OSQL -S MYPC -E -d MyDB -l 10 -Q "EXEC CheckDBRaiseError 'AG_DB_STORESQL'" -b -o c:\MyFolder\MSSQL_DB_CHECK.Log

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.