Marcia Q (10/16/2008)
If you run the checkdb command without any repair arguments, does it actually DO anything to the database? Is it correct to say that without a repair argument, it's only going to provide information?
Indeed. It checks for corruption and will write results into the error log.
CheckDB shouldn't be run with any of the repair options. They are there as a last resort if the corruption can't be fixed by rebuilding indexes or by restoring a clean backup.
What benefit would there be from running this with no_infomsgs on a nightly basis? And if even if I left off that argument, what benefit would there be to running it as nightly job? Does it write the checkdb results to the log?
With that option, it just reports on corruption. Without that, it will give you a list of tables that it's checked and the number of rows/pages each has. Personally I find that a waste of space and unnecessary. There are easier ways to see the rowcount/pagecount of a table.
Nightly may be a little more often than you need. I usually suggest once a week. If you have the time overnight, there's no harm in running it though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability