List the available fields in a DBCC command

  • The idea is to run DBCC in its various forms as part of a proactive maintenance program. Any reported errors will then be sent by Email to the relevant administrators for action. The results will have been placed into a temporary holding table and forwarded as required.

    My question is this: How do I discover what fields a DBCC command populates when it executes? Surely I need this information so I can build the relevant temporary holding tables for each DBCC run?

  • Execute it once and see?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • That is what I thought too but all I see is

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    I think I may have missed something......

  • There's nothing else if you scroll up? Which command are you running?

  • I ran this example with CHECKDB. And there is nothing else to scroll up to!

  • If this is a DBCC statement that only returns messages and you want to insert into a table, you have to use the TABLERESULTS option, otherwise it will only return messages, and messages can't be inserted into a table. Assuming the specific DBCC has the TABLERESULTS option. Many do, but not all.

    p.s. If it's checkDB you're after, run it without the NO_INFOMSGS to get the resultset shape.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I must have done something awry because now I see this....

    There are 89253 rows in 620 pages for object "Production.TransactionHistoryArchive".

    DBCC results for 'Production.ProductSubcategory'.

    There are 37 rows in 1 pages for object "Production.ProductSubcategory".

    DBCC results for 'Person.BusinessEntityContact'.

    There are 909 rows in 6 pages for object "Person.BusinessEntityContact".

    DBCC results for 'Purchasing.ProductVendor'.

    There are 460 rows in 5 pages for object "Purchasing.ProductVendor".

    DBCC results for 'Production.UnitMeasure'.

    There are 38 rows in 1 pages for object "Production.UnitMeasure".

    DBCC results for 'Person.ContactType'.

    There are 20 rows in 1 pages for object "Person.ContactType".

    DBCC results for 'Purchasing.Vendor'.

    There are 104 rows in 2 pages for object "Purchasing.Vendor".

    DBCC results for 'Sales.CountryRegionCurrency'.

    ....but nothing that returns the fields.

  • As I said above, if it's CheckDB you're running, you'll need to use the TABLERESULTS option, checkDB does not by default return a resultset. Other DBCC statements do, so it depends on which ones you're working with.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you! That is exactly what I was looking for!

  • You might want to attempt this:

    CREATE TABLE #LogData

    (CurrentLSNsysname varchar(50),Operationsysname varchar(50)

    ,Contextsysname varchar(50),

    TransactionIdsysname varchar(50),LogBlockGenerationint int)

    insert into #LogData EXEC ('DBCC LOG (QOD100)')

    select * from #LogData

    drop table #LogData

    -- typical response when I run the above on my system

    (7 row(s) affected)

    CurrentLSNsysname Operationsysname Contextsysname TransactionIdsysname LogBlockGenerationint

    ----------------------- ----------------- -------------------------------------------------- ---------

    0000008e:00000140:0002 LOP_BEGIN_CKPT LCX_NULL 0000:00000000 0

    0000008e:00000158:0001LOP_BEGIN_XACT LCX_NULL0000:0000097f 0

    0000008e:00000158:0002LOP_MODIFY_ROWLCX_BOOT_PAGE0000:0000097f 0

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply