List the available fields in a DBCC command

Question

List the available fields in a DBCC command

kevaburg

SSCoach

Points: 18131
More actions
September 20, 2013 at 6:15 am

#298951

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?

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

kevaburg SSCoach Points: 18131 More actions · Answer 2

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

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 3

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

kevaburg SSCoach Points: 18131 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

kevaburg SSCoach Points: 18131 More actions · Answer 6

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

kevaburg SSCoach Points: 18131 More actions · Answer 8

Thank you! That is exactly what I was looking for!

Ron McCullough SSC Guru Points: 63877 More actions · Answer 9

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]

List the available fields in a DBCC command

Cookies on SQLServerCentral