Run DBCC CheckDb with sqlcmd with error output

Centralize SQL Server maintenance with native tool in my goal. Scripts are saved in a database table, exported to a .bat in the file system and executed from a SQL Server Agent job.

The basic statement in the command batch file looks like this:

sqlcmd -E -S ServerName -Q “SET NOCOUNT ON; DBCC CHECKDB( 7) WITH NO_INFOMSGS ” 

I build the statement using the server database ID rather than database name.  There are no naming convention rules like “database names have no spaces” or “do not begin with a number” for systems not created by me.  Just easier to reference databases from numbers than names in the Dynamic SQL code within the stored procedures than handling complex database names.

I want to have a .log file error message result output.  I can then add a step to my job that looks for error message files and forward the file in email to me as notification of errors.

sqlcmd -E -S ServerName -Q “SET NOCOUNT ON; DBCC CHECKDB( 7) WITH ALL_ERRORMSGS, NO_INFOMSGS ” -o \\Lgc004\SQLBackups\Process\DbccCheckDb\Error\LG0113DBCCCheckDB.log                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

Paul Randal is a guide:

http://www.sqlskills.com/blogs/paul/how-to-get-all-the-corruption-messages-from-checkdb/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: