February 8, 2008 at 12:41 am
hi
I would like to catch the ouput from DBCC CHECKDB and pass it to my own logging system . and if there are errors perform tasks like notify the admin via email .. the problem I'm having to getting the output into a variable ... From what I have read so far the TRY / CATCH method will not work if there is an error because DBCC CHECKDB exits and the script end before reaching the CATCH ..
So how do I do this ...
February 8, 2008 at 4:54 am
CREATE stored procedure for DBCC CHECKDB and export the output of result to the text file. You can use other than sql server programme to read the error from the text file.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 8, 2008 at 11:52 am
I based this off of a script I found elsewhere (probably here)
CREATE TABLE CheckDBResults
(
RunDate datetime DEFAULT getdate(),
Error INT,
[Level] INT,
State INT,
MessageText VARCHAR(7000),
RepairLevel INT,
Status INT,
DbId INT,
ObjectId INT,
IndexId INT,
PartitionId INT,
AllocUnitId INT,
[File] INT,
Page INT,
Slot INT,
RefFile INT,
RefPage INT,
RefSlot INT,
Allocation INT
)
GO
CREATE PROCEDURE ssp_CheckDB
@DBCheck varchar(100),
@PhysicalOnly bit = 1 -- 1 for physical only, 0 for full
AS
DECLARE
@sqlstr varchar(250)
if @PhysicalOnly = 1
Set @sqlstr = 'DBCC CHECKDB ('+@DBCheck+') WITH PHYSICAL_ONLY, TABLERESULTS'
else
Set @sqlstr = 'DBCC CHECKDB ('+@DBCheck+') WITH TABLERESULTS'
INSERT INTO CheckDBResults
(Error,
[Level],
[State],
MessageText,
RepairLevel,
[Status],
[DbId],
ObjectId,
IndexId,
PartitionId,
AllocUnitId,
[File],
Page,
Slot,
RefFile,
RefPage,
RefSlot,
Allocation
)
EXEC(@sqlstr)
--Example:
--exec ssp_CheckDB 'TestDB', 0
--select
--RunDate,
--DB_Name(DbId) as DatabaseName,
--Object_Name(ObjectID) as ObjectName,
--MessageText,
--RepairLevel,
--Status
--from
--CheckDBResults
--ORDER BY RunDate, DatabaseName, ObjectName, RepairLevel DESC
--This select statement only works in 2K5
--truncate table CheckDBResults
March 25, 2008 at 5:35 am
hi ,
when i run dbcheck on databse , this delay several minutes , in thoses minutes the db is offline ??
thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply