Results 1 to 13 of 13
Thread: Dbcc Checkdb

121206, 10:45 #1Registered User
 Join Date
 Jul 2004
 Posts
 268
Unanswered: 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.

121206, 11:07 #2Window Washer
 Join Date
 Nov 2002
 Location
 Jersey
 Posts
 10,322
I was going for
Code:USE Northwind GO CREATE TABLE myTable99(Check_Log varchar(8000), Log_Time datetime DEFAULT GetDate()) GO INSERT INTO myTable99(Check_Log) DBCC CHECKDB
Brett
8)
It's a Great Day for America everybody!
dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was
The physical order of data in a database has no meaning.

121206, 11:47 #3Registered User
 Join Date
 Mar 2005
 Location
 Netherlands
 Posts
 280
I don't know what inka exactly wants but if you want to catch the output of DBCC CHECKDB for later evaluation and processing...
Code: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

121206, 11:57 #4Registered User
 Join Date
 Jul 2004
 Posts
 268
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.

121206, 15:18 #5Window Washer
 Join Date
 Nov 2002
 Location
 Jersey
 Posts
 10,322
Now why didn't I think about that...Damn DB2
Code: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
Brett
8)
It's a Great Day for America everybody!
dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was
The physical order of data in a database has no meaning.

121206, 15:18 #6Window Washer
 Join Date
 Nov 2002
 Location
 Jersey
 Posts
 10,322
Originally Posted by inkaBrett
8)
It's a Great Day for America everybody!
dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was
The physical order of data in a database has no meaning.

121206, 17:29 #7Registered User
 Join Date
 Jul 2004
 Posts
 268
I am not sure how the code that you sent me will create a bad table. Can you explain?
Thanks.

121206, 17:51 #8Registered User
 Join Date
 Mar 2005
 Location
 Netherlands
 Posts
 280
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.

121306, 11:56 #9Registered User
 Join Date
 Jul 2004
 Posts
 268
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.

121306, 13:10 #10Window Washer
 Join Date
 Nov 2002
 Location
 Jersey
 Posts
 10,322
Originally Posted by LexiflexBrett
8)
It's a Great Day for America everybody!
dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was
The physical order of data in a database has no meaning.

121306, 13:15 #11Window Washer
 Join Date
 Nov 2002
 Location
 Jersey
 Posts
 10,322
Originally Posted by inka
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
Code: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.
Brett
8)
It's a Great Day for America everybody!
dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was
The physical order of data in a database has no meaning.

121306, 14:21 #12Registered User
 Join Date
 Jul 2004
 Posts
 268
Do you have a sample checkdb output that has errors?

121306, 16:34 #13Registered User
 Join Date
 Mar 2005
 Location
 Netherlands
 Posts
 280
Originally Posted by Brett Kaiser
Originally Posted by inka