Results 1 to 13 of 13

Thread: Dbcc Checkdb

  1. #1
    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.

  2. #2
    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
    But it doesn't work
    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.

  3. #3
    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
    Looking at it I think this is a bit silly and there should be another way to do this!

  4. #4
    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.

  5. #5
    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.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by inka
    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?
    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.

  7. #7
    Join Date
    Jul 2004
    Posts
    268

    Unhappy

    I am not sure how the code that you sent me will create a bad table. Can you explain?

    Thanks.

  8. #8
    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.

  9. #9
    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.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Lexiflex
    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
    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.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by inka
    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

    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.

  12. #12
    Join Date
    Jul 2004
    Posts
    268
    Do you have a sample checkdb output that has errors?

  13. #13
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by Brett Kaiser
    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.

    Quote Originally Posted by inka
    Do you have a sample checkdb output that has errors?
    Luckily not at the moment But with a little googling you should find some...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •