September 20, 2013 at 6:15 am
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?
September 20, 2013 at 6:24 am
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
September 20, 2013 at 6:27 am
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......
September 20, 2013 at 6:32 am
There's nothing else if you scroll up? Which command are you running?
September 20, 2013 at 6:41 am
I ran this example with CHECKDB. And there is nothing else to scroll up to!
September 20, 2013 at 6:42 am
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
September 20, 2013 at 6:44 am
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.
September 20, 2013 at 6:45 am
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
September 20, 2013 at 6:46 am
Thank you! That is exactly what I was looking for!
September 20, 2013 at 7:14 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply