Grant Fritchey (7/31/2014)
cdm33.com (7/30/2014)
SQLRNNR,This code has hundreds of lines to it. I've got no clue what it does. I'm not going to put something like this on a production server not knowing what it does.
THanks anyway.
That is an excellent answer.
Jason is trustworthy and has probably pointed you at a good script, but I love your answer.
You can use the Maintenance Plans and SQL Agent to automate this easily. Just be extremely cautious about the Maintenance Plans. It'll look like they do everything for you short of make coffee in the morning, but they can be problematic. However, focused use, like just having it do DBCC checks, should be perfectly fine.
You'll find though, that over time, as you gain experience, the level of control with Maintenance Plans is too low. You'll eventually want to run scripts to get the job done.
There is that option. I see many problems with maintenance plans (even with strict focused use). One big problem is the Plan reporting success but actually doing nothing at all. That would be extremely frustrating as a DBA - especially if corruption did occur.
Another simple step would be to do a foreachdb that runs checkdb for each database. Sadly that means you might run into the foreachdb problem where it occasionally skips a database.
Another alternative is to write out the command for each database, put it in a separate job step and save that as a job. Then setup an operator and notifications for job failure. That doesn't get the checkdb results/output, which means coding something else to trap the results and report the results.
IMHO, if a simple solution is wanted and elegant results needed - it will take more lines of code. The simplest and most accurate of these options of course is the last option proposed where you script the checkdb for each database and put it into individual job steps.
😎
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events