Kludgeful DBCC CHECKDB WITH TABLERESULTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Kludgeful DBCC CHECKDB WITH TABLERESULTS

Author

Topic

Moussie
Starting Member

20 Posts

Posted - 2004-01-04 : 07:11:26

Hello!

I have some mediumsized databases that I want to automatically
run DBCC CHECKDB on during off hours and before backups.
Also, being lazy (yeah, trying to show off myself as
a good DBA :), I only want to be hassled if something
is wrong, not otherwise.

So, how do I redirect the output from DBCC CHECKDB ?
The best would be if I could pipe the output to a file
and then run some regexp in perl. But I have not found
a way to do that.

So then comes "DBCC CHECKDB WITH TABLERESULTS" along.
To which, of course, the output is not documented anywhere.

So my question is if the following code is safe:

[KLUDGE WARNING ON]
    create table result(col1 int,col2 int, col3 int, 
                        messagetext varchar(1000), 
                        col5 int,col6 int,col7 int, col8 int,
                        col9 int,col10 int,col11 int,col12 int,
                        col13 int,col14 int, col15 int, col16 int)

    insert into result exec('dbcc checkdb(@DBNAME) with tableresults')

    if (select count(*) from result where messagetext Like 
       '%0 allocation errors and 0 consistency error in database%'
        ) > 0
	
   EXEC SEND_EMAIL_WITH_WARNING

[/KLUDGE WARNING ON]

Either that, or are there other and better ways
to fetch the output of DBCC CHECKDB?

Best regards,
Moussie

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-01-04 : 12:59:11

You can always set up SQL scripts as files and run them using the osql utility (it's in Books Online, check it out) You can pipe osql's output to a file for later analysis too.

I can't remember all the ins and outs of DBCC CHECKDB's output, but I've found many of the DBCC commands don't even need WITH TABLERESULTS, you can just set up a table to match their output and do something like:

DECLARE @sql varchar(8000)
SET @sql='DBCC SHRINKFILE(2, 1024)'
INSERT INTO myDBCCResultsTable EXEC(@sql)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-05 : 14:23:43

Hmmm, why not just send a failure notice when the DBCC CHECKDB job fails? Why bother programmatically checking for failures when a job can do this for you?

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources