SQL Server Maintenance Scripts from Microsoft's DBAs
Download them here.
From the Word document in the zip;
"The Database Operations team at Microsoft uses a suite of jobs for all servers on-boarded and supported by the team, for performing backups, automated database maintenance, improve performance and to improve disaster recovery.
...
The following are the jobs get installed with this package.
SQLBackupAll – Performs Full database backup for non-excluded databases by SQL native or Litespeed backup commands. The job has parameters to specify the desired backup location and compress (SQL Litespeed backup) or nocompress (SQL native backup) , whether to backup or not to backup read only database etc.
SQLBackupDiffAll - Performs Differential database backup for non-excluded databases by SQL native or Litespeed backup commands. This job is disabled by default. This can be enabled based on the customer’s needs for the recovery requirements.
SQLBackupTranAll – Performs Transaction Log backup for non-excluded databases by SQL native or Litespeed backup commands. The job has parameters to specify the desired backup location and compress (SQL Litespeed backup) or nocompress (SQL native backup)
SQLBackupChecker – Checks for backups that have not occurred in more than the specified number of hours in the job. There is a parameter to specify the desired number of hours to specify for the alert.
SQLDBCCAll - Performs database consistency checks on all system and user databases.
SQLIndexDefragAll – Performs Index defrag maintenance for non-excluded SQL 2000 databases. This job is disabled by default. It can be enabled based on the customer’s needs. This job helps to run the index defrag for all the databases in the SQL instance or any particular database or any particular table in a database.
SQLUpdateStatistics - Runs Update Statistics maintenance for non-excluded databases. This job is disabled by default. This job is disabled by default. It can be enabled based on the customer’s needs. This job helps to run the update statistics for all the databases in the SQL instance or any particular database or any particular table in a database.
SQLCleanupMsdbBackupHistory – Cleans up the backup history information from msdb database.
SQLCycleErrorLog - Cycle the SQL Error log periodically so the size is manageable for viewing through SQL tools...."
Having played a pseudo-DBA role is past lives, I can appreciate the the value in not reinventing the DBA Admin wheel these scripts can provide. Many DBA's may have already written their own "Index Defrag's" or "Update Stat's" proc's, but still, it never hurts to see how someone else has written theirs...
Plus coming from MS, their might be some cool or interesting insights/implementations/stuff.
Posts
5 comments:
I don't see why you would want to use scripts such as these when you could just create a Database Maintenance Plan (DMP).
DMP do exactly what all these scripts do and has a nice interface to them.
In my current life as a developer, I find them interesting as they are easier to use on an ad-hoc basis than Plan's. Just fire up Query Analyzer and execute a given SP...
That and these are easier to tweak if need be.
But I do see your point. If Plan's work for you, then they work for you. I've also used Plan's in the past, as they ARE quick and easy to set up and get going...
The reason why you want to use scripts like this, is so that when you maintain 25+ sql servers, you can script your maintance plan to all your servers, rather than having to do all 25+ by hand in the DMP wizard. :)
Yes in Sql Server 2005 you could use the wizard to create a maint plan.
But we had a couple of databases wich where set to "SQL Server 2000 compatible" and those could not be selected in the wizard.
When changed the db compat. to "SQL Server 2005" they appeared and could be selected.
So we started doing this with scripts to have the same way for all databases.
And - when moving from one db machine to another we simply take the scripts with us. Easy do docu on, Easy to see what is going on.
No one has to make screen shots of the maint. dialogs to see settings.
When in a clustered environment and saving to anywhere but a local drive, maintenance plans don't work.
Post a Comment