Dbcc Checkdb

Hi All,

How can I make the dbcc checkdb fail so I can get the errors displayed in the report log? DBCC Checkdb is the step in the integrity job that I run once a month. What I am trying to do is when the dbcc checkdb fails for any reason, I want to get notified so I can correct the problem. I don't want to use repair fast or any other repair parameters that you can select when you run dbcc checkdb function because most of my dbs are 24x7.

Thanks.I was going for

USE Northwind
GO

CREATE TABLE myTable99(Check_Log varchar(8000), Log_Time datetime DEFAULT GetDate())
GO

INSERT INTO myTable99(Check_Log) DBCC CHECKDB

But it doesn't work|||I don't know what inka exactly wants but if you want to catch the output of DBCC CHECKDB for later evaluation and processing...
DECLARE @.cmd VARCHAR(255)
CREATE TABLE #result (txt VARCHAR(1000))
SET @.cmd = 'isql -SMySQLInstance -E -dNorthwind -Q"DBCC CHECKDB"'

INSERT #result
EXEC master..xp_cmdshell @.cmd

SELECT * FROM #result

DROP TABLE #result
Looking at it I think this is a bit silly :S and there should be another way to do this!|||One of the suggestions I was given was to create a bad table in a db and dbcc checkdb will report on it. I am not sure how to create a bad table so dbcc checkdb will have an error.|||Now why didn't I think about that...Damn DB2

DECLARE @.cmd VARCHAR(255)
CREATE TABLE #result (Log_Batch int, Log_Row int IDENTITY(1,1), Check_Log varchar(8000), Log_Time datetime DEFAULT GetDate())
SET @.cmd = 'osql -S<yourServerName> -E -dNorthwind -Q"DBCC CHECKDB"'

INSERT #result(Check_Log)
EXEC master..xp_cmdshell @.cmd

UPDATE #result SET Log_Batch = (SELECT MAX(COALESCE(Log_Batch,0))+1 FROM #result) WHERE Log_Batch IS NULL

IF EXISTS (SELECT * FROM #result o
WHERE Check_Log = 'CHECKDB found 0 allocation errors and 0 consistency errors in database'
AND Log_Batch IN (SELECT MAX(Log_Batch) FROM #result))
PRINT 'No Errors'
ELSE
PRINT 'Do some logic to page or email you'

DROP TABLE #result|||One of the suggestions I was given was to create a bad table in a db and dbcc checkdb will report on it. I am not sure how to create a bad table so dbcc checkdb will have an error.

Tell me your boss told you to do this, and if not who did?|||I am not sure how the code that you sent me will create a bad table. Can you explain?

Thanks.|||You can use the code Brett posted to signal you when DBCC CHECKDB generates an error. Creating a "bad" table (beats me how and why you want to do that on purpose, it will only create more problems) and running DBCC CHECKDB will always generate an error even when everything else is fine. And then you still have the problem of catching it.|||The reason I want to create a "bad" table and run dbcc checkdb is because I want to see what the error looks like. This will be one time deal and I am doing it in development. Once I see what the error looks like, then I will drop the table.|||running DBCC CHECKDB will always generate an error even when everything else is fine. And then you still have the problem of catching it.

Excuse me? Do you mean it will always produce outptut? I'm cornfused|||I am not sure how the code that you sent me will create a bad table. Can you explain?

Thanks.

Sure be glad to...it does not create a "bad" table (who told you to do this again?)...what id does do is tell you when you had an error...did you at least cut and paste the code and run it?

Sample CHECK DB utput below

DBCC results for 'Northwind'.
DBCC results for 'sysobjects'.
There are 295 rows in 5 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 243 rows in 11 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 1086 rows in 34 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 241 rows in 28 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 148 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 26 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 12 rows in 1 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 593 rows in 5 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 13 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'Orders'.
There are 830 rows in 20 pages for object 'Orders'.
DBCC results for 'Products'.
There are 77 rows in 1 pages for object 'Products'.
DBCC results for 'tempStringTest'.
There are 3 rows in 1 pages for object 'tempStringTest'.
DBCC results for 'dd3'.
There are 0 rows in 0 pages for object 'dd3'.
DBCC results for 'SEEDS1'.
There are 11 rows in 1 pages for object 'SEEDS1'.
DBCC results for 'SEEDS2'.
There are 4 rows in 1 pages for object 'SEEDS2'.
DBCC results for 'fund_info'.
There are 0 rows in 0 pages for object 'fund_info'.
DBCC results for 'fund_history'.
There are 29510 rows in 325 pages for object 'fund_history'.
DBCC results for 'fund_nav_info'.
There are 93 rows in 2 pages for object 'fund_nav_info'.
DBCC results for 'ProcessLog'.
There are 9 rows in 1 pages for object 'ProcessLog'.
DBCC results for 'Order Details'.
There are 2155 rows in 9 pages for object 'Order Details'.
DBCC results for 'TMP_Order Details_2005_08_16_13_32_30'.
There are 2155 rows in 10 pages for object 'TMP_Order Details_2005_08_16_13_32_30'.
DBCC results for 'TMP_Order Details_2005_08_16_13_32_54'.
There are 2155 rows in 10 pages for object 'TMP_Order Details_2005_08_16_13_32_54'.
DBCC results for 'TMP_Order Details_2005_08_16_13_33_00'.
There are 2155 rows in 10 pages for object 'TMP_Order Details_2005_08_16_13_33_00'.
DBCC results for 'TMP_Order Details_2005_08_16_13_33_03'.
There are 2155 rows in 10 pages for object 'TMP_Order Details_2005_08_16_13_33_03'.
DBCC results for 'data1'.
There are 6 rows in 1 pages for object 'data1'.
DBCC results for 'testPrimary'.
There are 4 rows in 1 pages for object 'testPrimary'.
DBCC results for 'testRelated'.
There are 6 rows in 1 pages for object 'testRelated'.
DBCC results for 'emp'.
There are 0 rows in 0 pages for object 'emp'.
DBCC results for 'myAgents99'.
There are 14 rows in 1 pages for object 'myAgents99'.
DBCC results for 'myCustomers99'.
There are 5 rows in 1 pages for object 'myCustomers99'.
DBCC results for 'patients'.
There are 9 rows in 1 pages for object 'patients'.
DBCC results for 'LOG_TABLE'.
There are 1 rows in 1 pages for object 'LOG_TABLE'.
DBCC results for 'Trace_D060911'.
There are 36 rows in 1 pages for object 'Trace_D060911'.
DBCC results for 'spid_Sleep'.
There are 29 rows in 1 pages for object 'spid_Sleep'.
DBCC results for 'myTable99'.
There are 0 rows in 0 pages for object 'myTable99'.
DBCC results for 'X105002.myorders'.
There are 830 rows in 21 pages for object 'X105002.myorders'.
DBCC results for 'X105002.jimtable1'.
There are 825 rows in 21 pages for object 'X105002.jimtable1'.
DBCC results for 'tblStateProv'.
There are 0 rows in 0 pages for object 'tblStateProv'.
DBCC results for 'tblUserDetails'.
There are 0 rows in 0 pages for object 'tblUserDetails'.
DBCC results for 'Periods'.
There are 3 rows in 1 pages for object 'Periods'.
DBCC results for 'CustomerCustomerDemo'.
There are 0 rows in 0 pages for object 'CustomerCustomerDemo'.
DBCC results for 'CustomerDemographics'.
There are 0 rows in 0 pages for object 'CustomerDemographics'.
DBCC results for 'Region'.
There are 4 rows in 1 pages for object 'Region'.
DBCC results for 'Territories'.
There are 53 rows in 1 pages for object 'Territories'.
DBCC results for 'EmployeeTerritories'.
There are 49 rows in 1 pages for object 'EmployeeTerritories'.
DBCC results for '1GEN_PROFILE_INS_060711'.
There are 106 rows in 2 pages for object '1GEN_PROFILE_INS_060711'.
DBCC results for 'TMPPARTMSTR'.
There are 4 rows in 1 pages for object 'TMPPARTMSTR'.
DBCC results for 'a'.
There are 340 rows in 4 pages for object 'a'.
DBCC results for 'TMPPRODUCT_MASTER'.
There are 3 rows in 1 pages for object 'TMPPRODUCT_MASTER'.
DBCC results for 'sp_depends_xref'.
There are 462 rows in 8 pages for object 'sp_depends_xref'.
DBCC results for 'n'.
There are 999 rows in 2 pages for object 'n'.
DBCC results for 'dd'.
There are 0 rows in 0 pages for object 'dd'.
DBCC results for 'ProductLocation'.
There are 2 rows in 1 pages for object 'ProductLocation'.
DBCC results for 'dd1'.
There are 0 rows in 0 pages for object 'dd1'.
DBCC results for 'Synergy_Signon'.
There are 0 rows in 0 pages for object 'Synergy_Signon'.
DBCC results for 'dtproperties'.
There are 0 rows in 0 pages for object 'dtproperties'.
DBCC results for 'dd2'.
There are 0 rows in 0 pages for object 'dd2'.
DBCC results for 'myDST99'.
There are 26 rows in 1 pages for object 'myDST99'.
DBCC results for 'Trigger_Log'.
There are 0 rows in 0 pages for object 'Trigger_Log'.
DBCC results for 'myTrades99'.
There are 9 rows in 1 pages for object 'myTrades99'.
DBCC results for 'Synergy_Signon2'.
There are 1019 rows in 12 pages for object 'Synergy_Signon2'.
DBCC results for 'myReceipts99'.
There are 12 rows in 1 pages for object 'myReceipts99'.
DBCC results for 'Sproc_Log'.
There are 0 rows in 0 pages for object 'Sproc_Log'.
DBCC results for 'Joe99'.
There are 8 rows in 1 pages for object 'Joe99'.
DBCC results for 'Trace_D061003'.
There are 24 rows in 1 pages for object 'Trace_D061003'.
DBCC results for 'tM_FR_STOPS'.
There are 14 rows in 1 pages for object 'tM_FR_STOPS'.
DBCC results for 'module'.
There are 2 rows in 1 pages for object 'module'.
DBCC results for 'tM_FR_WIP_UnitID_FastStops'.
There are 2 rows in 1 pages for object 'tM_FR_WIP_UnitID_FastStops'.
DBCC results for 'module_content'.
There are 2 rows in 1 pages for object 'module_content'.
DBCC results for 'Directory_Contents_Stage'.
There are 10 rows in 1 pages for object 'Directory_Contents_Stage'.
DBCC results for 'myTableSearch99'.
There are 0 rows in 0 pages for object 'myTableSearch99'.
DBCC results for 'page'.
There are 2 rows in 1 pages for object 'page'.
DBCC results for 'Directory_Contents'.
There are 68 rows in 1 pages for object 'Directory_Contents'.
DBCC results for 'role'.
There are 2 rows in 1 pages for object 'role'.
DBCC results for 'role_permissions'.
There are 1 rows in 1 pages for object 'role_permissions'.
DBCC results for 'user_permissions'.
There are 1 rows in 1 pages for object 'user_permissions'.
DBCC results for 'tt'.
There are 4 rows in 1 pages for object 'tt'.
DBCC results for 'CONFIG_CODE'.
There are 0 rows in 0 pages for object 'CONFIG_CODE'.
DBCC results for 'Organization'.
There are 0 rows in 0 pages for object 'Organization'.
DBCC results for 'SCRAMBLE_SYSTEM'.
There are 0 rows in 0 pages for object 'SCRAMBLE_SYSTEM'.
DBCC results for 'Trace'.
There are 10515 rows in 231 pages for object 'Trace'.
DBCC results for 'SCRAMBLE_FILE'.
There are 0 rows in 0 pages for object 'SCRAMBLE_FILE'.
DBCC results for 'CONFIG_FILE'.
There are 0 rows in 0 pages for object 'CONFIG_FILE'.
DBCC results for 'trace2'.
There are 10515 rows in 231 pages for object 'trace2'.
DBCC results for 'Employees'.
There are 9 rows in 1 pages for object 'Employees'.
DBCC results for 'myOrg99'.
There are 6 rows in 1 pages for object 'myOrg99'.
DBCC results for 'GroupRules'.
There are 0 rows in 1 pages for object 'GroupRules'.
DBCC results for 'Categories'.
There are 8 rows in 1 pages for object 'Categories'.
DBCC results for 'Answers'.
There are 0 rows in 1 pages for object 'Answers'.
DBCC results for 'myAccounts99'.
There are 3 rows in 1 pages for object 'myAccounts99'.
DBCC results for 'Customers'.
There are 91 rows in 3 pages for object 'Customers'.
DBCC results for 'Shippers'.
There are 3 rows in 1 pages for object 'Shippers'.
DBCC results for 'Mark99'.
There are 25 rows in 1 pages for object 'Mark99'.
DBCC results for 'Suppliers'.
There are 29 rows in 1 pages for object 'Suppliers'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'Northwind'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.|||Do you have a sample checkdb output that has errors?|||Excuse me? Do you mean it will always produce outptut? I'm cornfused
I meant that if you have a bad table it will always produce an error. The point of having a bad table was unclear to me.

Do you have a sample checkdb output that has errors?
Luckily not at the moment ;) But with a little googling you should find some...