capture DBCC checkDB result in scheduled tasks

[Wing Szeto]

I schedule dbcc checkdb command in the SQL schedule task program at 3:00am. It ran successfully but since it ran as a schedule task, I don't know where to find the results. Can anyone help?

Thanks in advance for any help.

Wing

[Kenneth Wilhelmsson]

Hi Wing.

You could
1) run DBCC through ISQL with the -o switch to specify outputfile
2) create a table with a varchar(255) column, and then do
INSERT yourlogtable EXEC ( 'DBCC CHECKDB(yourdatabase)' )

/Kenneth

------------
Wing Szeto at 4/22/99 6:39:32 PM

I schedule dbcc checkdb command in the SQL schedule task program at 3:00am. It ran successfully but since it ran as a schedule task, I don't know where to find the results. Can anyone help?

Thanks in advance for any help.

Wing

[Chris]

Kenneth, Are you sure about number 2 below? I tried running it about 7 dozen different ways and couldn't get it to work. Is that
the right syntax?
Thank you,
Chris


------------
Kenneth Wilhelmsson at 4/23/99 2:46:18 AM

Hi Wing.

You could
1) run DBCC through ISQL with the -o switch to specify outputfile
2) create a table with a varchar(255) column, and then do
INSERT yourlogtable EXEC ( 'DBCC CHECKDB(yourdatabase)' )

/Kenneth

------------
Wing Szeto at 4/22/99 6:39:32 PM

I schedule dbcc checkdb command in the SQL schedule task program at 3:00am. It ran successfully but since it ran as a schedule task, I don't know where to find the results. Can anyone help?

Thanks in advance for any help.

Wing

[Kenneth Wilhelmsson]

Chris,

I stand corrected.
The reason 2) won't work is that if no errors are enountered, DBCC CHECKDB returns zero rows, and you have nothing to insert.
I'm not quite sure if there are rows returned on errors. In that case, if table is empty, no errors, otherwise there were errors.
I did some checking on DBCC checkdb, checktable, newalloc and checkctatalog and they all return 0 rows when successful.

If you want to scan the output anyway, then you have to do 1) and take it from there.

/Kenneth

------------
Chris at 4/26/99 1:54:20 PM

Kenneth, Are you sure about number 2 below? I tried running it about 7 dozen different ways and couldn't get it to work. Is that
the right syntax?
Thank you,
Chris


------------
Kenneth Wilhelmsson at 4/23/99 2:46:18 AM

Hi Wing.

You could
1) run DBCC through ISQL with the -o switch to specify outputfile
2) create a table with a varchar(255) column, and then do
INSERT yourlogtable EXEC ( 'DBCC CHECKDB(yourdatabase)' )

/Kenneth

------------
Wing Szeto at 4/22/99 6:39:32 PM

I schedule dbcc checkdb command in the SQL schedule task program at 3:00am. It ran successfully but since it ran as a schedule task, I don't know where to find the results. Can anyone help?

Thanks in advance for any help.

Wing

[MarceloGSilva]

By Marcelo Girao Silva:

create table dbo.results (line varchar(8000))

exec master..xp_cmdshell 'osql -S MYSERVER -E -d AdventureWorks -Q "dbcc checkdb" -o c:\checkdb.txt'

truncate table results

exec master..xp_cmdshell 'bcp Results in "c:\checkdb.txt" -c -S MYSERVER -T -d DBA '

declare @summary varchar(800), @allocationerrors int, @consistencyerrors int

select @summary = line from results where line like 'CHECKDB found % allocation errors and % consistency errors in database%'

select @allocationerrors = SUBSTRING(@summary, 14, CHARINDEX('allocation', @summary)-14)

select @consistencyerrors = SUBSTRING(@summary, CHARINDEX(' and ', @summary) + 5, CHARINDEX('consistency', @summary) - CHARINDEX(' and ', @summary)-5)

if (@allocationerrors + @consistencyerrors) > 0
select @summary

[Steve R Jones]

Marcelo - did you happen to notice that this thread is over TEN Years old

[MarceloGSilva]

Better late than never :-)
BTW here goes a more elegant solution using the new with tableresults feature:


if OBJECT_ID('[dbcc_history]') is not null
drop table [dbcc_history]
go

CREATE TABLE [dbo].[dbcc_history](
[Error] varchar(max) NULL,
[Level] varchar(max) NULL,
[State] varchar(max) NULL,
[MessageText] [varchar](max) NULL,
[RepairLevel] varchar(max) NULL,
[Status] varchar(max) NULL,
[DbId] varchar(max) NULL,
[Id] varchar(max) NULL,
[IndId] varchar(max) NULL,
[PartitionID] varchar(max)NULL,
[AllocUnitID] varchar(max) NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [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]
GO

if OBJECT_ID('[usp_CheckDBIntegrity]') is not null
DROP PROC [usp_CheckDBIntegrity]
GO

CREATE PROC [dbo].[usp_CheckDBIntegrity] (@databases varchar(max)='')
AS
declare @sql varchar(max), @db varchar(128)

if left(@databases,1) <> ','
set @databases = ',' + @databases

if right(@databases,1) <> ','
set @databases = @databases + ','

DECLARE database_cursor CURSOR FOR
SELECT name
FROM sys.databases db
WHERE name NOT IN ('master','model','msdb','tempdb')
AND db.state_desc = 'ONLINE'
AND source_database_id IS NULL -- REAL DBS ONLY (Not Snapshots)
AND is_read_only = 0
and charindex(name, @databases) > 0

OPEN database_cursor
FETCH next FROM database_cursor INTO @db
WHILE @@FETCH_STATUS=0
BEGIN
set @sql = 'dbcc checkdb(''' + @db + ''') with tableresults'

print @sql

INSERT INTO dbcc_history ([Error], [Level], [State], MessageText, RepairLevel, [Status],
[DbId], Id, IndId, PartitionId, AllocUnitId, [File], Page, Slot, RefFile, RefPage, RefSlot,Allocation)
EXEC (@sql)

FETCH next FROM database_cursor INTO @db
END

CLOSE database_cursor
DEALLOCATE database_cursor
GO

EXEC usp_CheckDBIntegrity 'AdventureWorks'

declare @summary varchar(800), @allocationerrors int, @consistencyerrors int

select @summary = MessageText from [dbcc_history] where MessageText like 'CHECKDB found % allocation errors and % consistency errors in database%'

select @allocationerrors = SUBSTRING(@summary, 14, CHARINDEX('allocation', @summary)-14)

select @consistencyerrors = SUBSTRING(@summary, CHARINDEX(' and ', @summary) + 5, CHARINDEX('consistency', @summary) - CHARINDEX(' and ', @summary)-5)

if (@allocationerrors + @consistencyerrors) > 0
select @summary