Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

List the available fields in a DBCC command Expand / Collapse
Author
Message
Posted Friday, September 20, 2013 6:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
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?
Post #1496836
Posted Friday, September 20, 2013 6:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:58 PM
Points: 42,466, Visits: 35,532
Execute it once and see?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1496840
Posted Friday, September 20, 2013 6:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
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......
Post #1496844
Posted Friday, September 20, 2013 6:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:56 AM
Points: 451, Visits: 1,665
There's nothing else if you scroll up? Which command are you running?
Post #1496847
Posted Friday, September 20, 2013 6:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
I ran this example with CHECKDB. And there is nothing else to scroll up to!
Post #1496851
Posted Friday, September 20, 2013 6:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:58 PM
Points: 42,466, Visits: 35,532
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 2008, MVP
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

Post #1496852
Posted Friday, September 20, 2013 6:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
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.
Post #1496853
Posted Friday, September 20, 2013 6:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:58 PM
Points: 42,466, Visits: 35,532
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 2008, MVP
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

Post #1496854
Posted Friday, September 20, 2013 6:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
Thank you! That is exactly what I was looking for!
Post #1496855
Posted Friday, September 20, 2013 7:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:36 PM
Points: 5,572, Visits: 24,800
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
Post #1496875
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse