Tales from the Doghouse - Cool DBA Automation Jobs

Monday, June 18, 2007

I saw a demo of these at Tech-Ed, and thought I would share with my friends.

Essentially, the Database Operations team at Microsoft uses a suite of jobs on all their servers for performing backups, automated database maintenance, improve performance , and improving disaster recovery. I'm sure every SQL Server expert/consultant out there has his or her own bag of tricks, but Microsoft is finally sharing theirs.

These SQL maintenance jobs provide automated tasks such as database or transaction logs backup, performing database optimization like Index defrag or update statistics, cleaning MSDB backup history and recycling the SQL error log. Each of these jobs runs stored procedures that are installed when the jobs are created.

The following are the jobs get installed with this package.

SQLBackupAll
SQLBackupDiffAll
SQLBackupTranAll
SQLBackupChecker
SQLDBCCAll
SQLIndexDefragAll
SQLUpdateStatistics
SQLCleanupMsdbBackupHistory
SQLCycleErrorLog

Download the automation jobs here, and then refer to the Word document inside.

Monday, June 18, 2007 4:16:54 PM (Mountain Daylight Time, UTC-06:00)

Comments [2] -
SQL Server | SQLblog

Monday, June 18, 2007 4:24:45 PM (Mountain Daylight Time, UTC-06:00)

ohhh, cool...wait, wait (smack!)

I have to keep repeating: I'm not a dba, I'm not a dba.

Chris Brandsma

Monday, February 04, 2008 11:21:06 AM (Mountain Standard Time, UTC-07:00)

Thanks for the scripts. I did create a new one that updates usage as well. I hope you have time for a couple questions.

I am running SQL 2000 enterprise, currently sp3a. Some of my servers
are heavy used production boxes and I need to be able to manage the
indexes in a systematic way.

My questions are:

If I run this nightly

1: Should I update all statistics or just on the tables I have
defragged?

2: If only on the Defragged tables, should I make another job to
update statisics for all user databases Weekly/monthly/not at all?

3: How often should I update usage

4: How often should I run CheckDB, and with what options?

Thanks!

David Hay | david dot hayAT NOSPAMgmail dot com

Name
E-mail
Home page

	Remember Me
Comment (HTML not allowed)
Enter the code shown (prevents robots):