SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


List the available fields in a DBCC command


List the available fields in a DBCC command

Author
Message
kevaburg
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 988
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86356 Visits: 45232
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
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 988
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
Beatrix Kiddo
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2670 Visits: 4391
There's nothing else if you scroll up? Which command are you running?
kevaburg
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 988
I ran this example with CHECKDB. And there is nothing else to scroll up to!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86356 Visits: 45232
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
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 988
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86356 Visits: 45232
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
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 988
Thank you! That is exactly what I was looking for!
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7773 Visits: 25280
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:0001 LOP_BEGIN_XACT LCX_NULL 0000:0000097f 0
0000008e:00000158:0002 LOP_MODIFY_ROW LCX_BOOT_PAGE 0000: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

Before posting a performance problem please read
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search