January 31, 2017 at 4:02 am
Hi,
my Goal is to insert the results from a DBCC CHECKDB into a table that will hold 6 months worth of data (the customer requirement).
Here the table:
create table dbo.tbl_axs_DBCC (
RecordDate datetime default getdate(),
Text nvarchar(1000)
);
Here the statement to populate the table:
begin transaction
insert into dbo.tbl_axs_DBCC (Text)
exec ('dbcc checkdb(AdventureWorks2012);');
commit;
The DBCC statement runs flawlessly and the results are displayed onscreen but the table remains empty and I can't understand why.
I have also tried inserting the DBCC results into a table with a single column but still nothing. I am pretty sure I have forgotten something simple.....
Has anyone got any ideas?
January 31, 2017 at 4:11 am
kevaburg - Tuesday, January 31, 2017 4:02 AMHi,my Goal is to insert the results from a DBCC CHECKDB into a table that will hold 6 months worth of data (the customer requirement).
Here the table:
create table dbo.tbl_axs_DBCC (
RecordDate datetime default getdate(),
Text nvarchar(1000)
);Here the statement to populate the table:
begin transaction
insert into dbo.tbl_axs_DBCC (Text)
exec ('dbcc checkdb(AdventureWorks2012);');
commit;The DBCC statement runs flawlessly and the results are displayed onscreen but the table remains empty and I can't understand why.
I have also tried inserting the DBCC results into a table with a single column but still nothing. I am pretty sure I have forgotten something simple.....
Has anyone got any ideas?
I don't think that it shows the results as product of select statement. Instead it is using something like print, but can you also explain why do you want to capture those results? As far as I understand if everything went well, the messages that you get are mainly about the number of pages and rows in tables and if it is important for you, then you can get it in other ways.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 31, 2017 at 4:26 am
Adi Cohn-120898 - Tuesday, January 31, 2017 4:11 AMkevaburg - Tuesday, January 31, 2017 4:02 AMHi,my Goal is to insert the results from a DBCC CHECKDB into a table that will hold 6 months worth of data (the customer requirement).
Here the table:
create table dbo.tbl_axs_DBCC (
RecordDate datetime default getdate(),
Text nvarchar(1000)
);Here the statement to populate the table:
begin transaction
insert into dbo.tbl_axs_DBCC (Text)
exec ('dbcc checkdb(AdventureWorks2012);');
commit;The DBCC statement runs flawlessly and the results are displayed onscreen but the table remains empty and I can't understand why.
I have also tried inserting the DBCC results into a table with a single column but still nothing. I am pretty sure I have forgotten something simple.....
Has anyone got any ideas?
I don't think that it shows the results as product of select statement. Instead it is using something like print, but can you also explain why do you want to capture those results? As far as I understand if everything went well, the messages that you get are mainly about the number of pages and rows in tables and if it is important for you, then you can get it in other ways.
Adi
Due to a recent problem whereby corruption went unnoticed for several weeks, our customer wants to store historical information from the DBCC in a table. that can reference the last 6 months. There is no other way to achieve this other than to increase the error log Retention time, something that is undesireable due to the amount of space it requires for such a protracted time span.
I do know it is possible to do, it's just that this time it doesn't work......
January 31, 2017 at 4:32 am
Quick thought, if it runs clean with no errors then there is an entry in the sql server log, if there is a corruption, look for (and archive) the SQLDUMPnnnn.txt file in the SQL Server LOG directory.
😎
January 31, 2017 at 4:36 am
kevaburg - Tuesday, January 31, 2017 4:26 AMAdi Cohn-120898 - Tuesday, January 31, 2017 4:11 AMkevaburg - Tuesday, January 31, 2017 4:02 AMHi,my Goal is to insert the results from a DBCC CHECKDB into a table that will hold 6 months worth of data (the customer requirement).
Here the table:
create table dbo.tbl_axs_DBCC (
RecordDate datetime default getdate(),
Text nvarchar(1000)
);Here the statement to populate the table:
begin transaction
insert into dbo.tbl_axs_DBCC (Text)
exec ('dbcc checkdb(AdventureWorks2012);');
commit;The DBCC statement runs flawlessly and the results are displayed onscreen but the table remains empty and I can't understand why.
I have also tried inserting the DBCC results into a table with a single column but still nothing. I am pretty sure I have forgotten something simple.....
Has anyone got any ideas?
I don't think that it shows the results as product of select statement. Instead it is using something like print, but can you also explain why do you want to capture those results? As far as I understand if everything went well, the messages that you get are mainly about the number of pages and rows in tables and if it is important for you, then you can get it in other ways.
Adi
Due to a recent problem whereby corruption went unnoticed for several weeks, our customer wants to store historical information from the DBCC in a table. that can reference the last 6 months. There is no other way to achieve this other than to increase the error log Retention time, something that is undesireable due to the amount of space it requires for such a protracted time span.
I do know it is possible to do, it's just that this time it doesn't work......
As suggested here, you may be able to grab this info by invoking the command from a .NET app. The results you are seeing in SSMS when running this command are not generated by SELECTs.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 31, 2017 at 4:45 am
Phil Parkin - Tuesday, January 31, 2017 4:36 AMAs suggested here, you may be able to grab this info by invoking the command from a .NET app. The results you are seeing in SSMS when running this command are not generated by SELECTs.
Yes, or use xp_cmdshell to call a PowerShell command and insert the results of that into a table.
John
January 31, 2017 at 4:50 am
Eirikur Eiriksson - Tuesday, January 31, 2017 4:32 AMQuick thought, if it runs clean with no errors then there is an entry in the sql server log, if there is a corruption, look for (and archive) the SQLDUMPnnnn.txt file in the SQL Server LOG directory.
😎
It is a good idea that didn't go unnoticed but the Problem is that it Needs to run automatically. The customer won't pay for us to simply look at the logs and decide whether or not a file should be archived or not.
That said your idea is still good.....I think I will see if I can resolve the issue with xp_readerrorlog....
January 31, 2017 at 4:54 am
John Mitchell-245523 - Tuesday, January 31, 2017 4:45 AMPhil Parkin - Tuesday, January 31, 2017 4:36 AMAs suggested here, you may be able to grab this info by invoking the command from a .NET app. The results you are seeing in SSMS when running this command are not generated by SELECTs.Yes, or use xp_cmdshell to call a PowerShell command and insert the results of that into a table.
John
I am considering a solution using xp_readerrorlog to extract what I Need from the current logfile and insert that into the table......
January 31, 2017 at 5:00 am
kevaburg - Tuesday, January 31, 2017 4:54 AMI am considering a solution using xp_readerrorlog to extract what I Need from the current logfile and insert that into the table......
OK, as long as that works for you. If there is corruption in the database, will there be enough information in the errorlog to identify it? I've a feeling (but I don't know for sure) that you'll get more information in the output of DBCC CHECKDB than you will in the errorlog.
John
January 31, 2017 at 5:39 am
Pitching in some more, normally I use Ola Hallengren's Maintenance Solutions for running the integrity checks and use the option of logging to table. This then allows me to plug the results into both monitoring systems and health reporting. Further, I also include the msdb.dbo.suspect_pages in the monitoring, just in case.
😎
January 31, 2017 at 6:56 am
DECLARE @DBCC NVARCHAR(256) ;
DECLARE @Database_Name NVARCHAR(50);
CREATE TABLE #DBCC
(
[Error] VARCHAR(255) ,
[Level] VARCHAR(255) ,
[State] VARCHAR(255) ,
[MessageText] VARCHAR(255) ,
[RepairLevel] VARCHAR(255) ,
[Status] VARCHAR(255) ,
[DBId] VARCHAR(255) ,
[DBFragId] VARCHAR(255) ,
[ObjectId] VARCHAR(255) ,
[IndexId] VARCHAR(255) ,
[PartitionId] VARCHAR(255) ,
[AllocUnitId] VARCHAR(255) ,
[RIdDBId] VARCHAR(255) ,
[RIdPruId] VARCHAR(255) ,
[File] VARCHAR(255) ,
[Page] VARCHAR(255) ,
[Slot] VARCHAR(255) ,
[RefDBId] VARCHAR(255) ,
[RefPruId] VARCHAR(255) ,
[RefFile] VARCHAR(255) ,
[RefPage] VARCHAR(255) ,
[RefSlot] VARCHAR(255) ,
[Allocation] VARCHAR(255)
);
SET @Database_Name = 'master';
SELECT @DBCC = 'DBCC CHECKDB ([' + @Database_Name
+ ']) WITH ALL_ERRORMSGS, TABLERESULTS';
INSERT INTO #DBCC
( Error ,
[Level] ,
[State] ,
MessageText ,
RepairLevel ,
[Status] ,
[DBId] ,
DBFragId ,
ObjectId ,
IndexId ,
PartitionId ,
AllocUnitId ,
RIdDBId ,
RIdPruId ,
[File] ,
[Page] ,
Slot ,
RefDBId ,
RefPruId ,
RefFile ,
RefPage ,
RefSlot ,
Allocation )
EXEC ( @DBCC );
SELECT * FROM #DBCC;
January 31, 2017 at 7:03 am
BrainDonor - Tuesday, January 31, 2017 6:56 AM
DECLARE @DBCC NVARCHAR(256) ;
DECLARE @Database_Name NVARCHAR(50);CREATE TABLE #DBCC
(
[Error] VARCHAR(255) ,
[Level] VARCHAR(255) ,
[State] VARCHAR(255) ,
[MessageText] VARCHAR(255) ,
[RepairLevel] VARCHAR(255) ,
[Status] VARCHAR(255) ,
[DBId] VARCHAR(255) ,
[DBFragId] VARCHAR(255) ,
[ObjectId] VARCHAR(255) ,
[IndexId] VARCHAR(255) ,
[PartitionId] VARCHAR(255) ,
[AllocUnitId] VARCHAR(255) ,
[RIdDBId] VARCHAR(255) ,
[RIdPruId] VARCHAR(255) ,
[File] VARCHAR(255) ,
[Page] VARCHAR(255) ,
[Slot] VARCHAR(255) ,
[RefDBId] VARCHAR(255) ,
[RefPruId] VARCHAR(255) ,
[RefFile] VARCHAR(255) ,
[RefPage] VARCHAR(255) ,
[RefSlot] VARCHAR(255) ,
[Allocation] VARCHAR(255)
);SET @Database_Name = 'master';
SELECT @DBCC = 'DBCC CHECKDB ([' + @Database_Name
+ ']) WITH ALL_ERRORMSGS, TABLERESULTS';INSERT INTO #DBCC
( Error ,
[Level] ,
[State] ,
MessageText ,
RepairLevel ,
[Status] ,
[DBId] ,
DBFragId ,
ObjectId ,
IndexId ,
PartitionId ,
AllocUnitId ,
RIdDBId ,
RIdPruId ,
[File] ,
[Page] ,
Slot ,
RefDBId ,
RefPruId ,
RefFile ,
RefPage ,
RefSlot ,
Allocation )
EXEC ( @DBCC );SELECT * FROM #DBCC;
I thought there was a parameter for results to table! However, it doesn't appear to be documented.
John
January 31, 2017 at 7:19 am
John Mitchell-245523 - Tuesday, January 31, 2017 7:03 AMI thought there was a parameter for results to table! However, it doesn't appear to be documented.John
Yes, there are a few blog sites that mention it but you won't find it officially documented. I found it when working out how to automate my DBCC checks against backups.
February 1, 2017 at 5:50 pm
Here's how I have set it up (with thanks to whoever provided the original code)
1. Create table for CheckDB results
2. Create store proc to apply CheckDB to each database
3. Create scheduled job to run stored proc, with a second step to email the results to DBA (code not provided for this step)
-- create table ---------------------------------------------------------------------
CREATE TABLE [dbo].[dbcc_results](
[Error] [int] NULL,
[Level] [int] NULL,
[State] [int] NULL,
[MessageText] [varchar](7000) NULL,
[RepairLevel] [int] NULL,
[Status] [int] NULL,
[DbId] [int] NULL,
[DbFragId] [int] NULL,
[ObjectId] [int] NULL,
[IndexId] [int] NULL,
[PartitionID] [int] NULL,
[AllocUnitID] [int] NULL,
[RidDbId] [int] NULL,
[RidPruId] [int] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[RefDbId] [int] NULL,
[RefPruId] [int] NULL,
[RefFile] [int] NULL,
[RefPage] [int] NULL,
[RefSlot] [int] NULL,
[Allocation] [int] NULL,
[TimeStamp] [datetime] NULL CONSTRAINT [DF_dbcc_history_TimeStamp] DEFAULT (getdate())
) ON
[PRIMARY]
-- create stored proc ----------------------------------------------------------------
CREATE PROC [dbo].[usp_CheckDBIntegrity]
@database_name SYSNAME=NULL
AS
IF @database_name IS NULL -- Run against all databases
BEGIN
DECLARE database_cursor CURSOR FOR
SELECT name
FROM sys.databases db
WHERE db.state_desc = 'ONLINE'
AND source_database_id IS NULL -- 'real' DBS ONLY (No database snapshots)
AND is_read_only = 0
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @database_name
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO dbcc_results (Error, [Level], [State], MessageText,RepairLevel, [Status],
DbId, DbFragId,ObjectId, IndexId, PartitionId,AllocUnitId, RidDbId, RidPruId,[File],
[Page], Slot,RefDbId, RefPruId, RefFile,RefPage, RefSlot, Allocation)
EXEC ('dbcc checkdb(''' + @database_name + ''') with TABLERESULTS, ALL_ERRORMSGS')-- includes all infomsgs by default (subsequently deleted except for summary - alternatively use the 'with No_INFOMSGS' option)
FETCH NEXT FROM database_cursor INTO @database_name
END
DELETE FROM dbcc_history
WHERE ERROR IN(2593, 8997)-- delete informational messages
CLOSE database_cursor
DEALLOCATE database_cursor
END
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply