-
capture DBCC checkDB result in scheduled tasks
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
-
capture DBCC checkDB result in scheduled tasks (reply)
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
-
capture DBCC checkDB result in scheduled tasks (reply)
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
-
capture DBCC checkDB result in scheduled tasks (reply)
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
-
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
-
Marcelo - did you happen to notice that this thread is over TEN Years old
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|