Code Camp Presentation - Automating SQL Maintenance and Backups

I'm finally getting my presentation and database maintenance and backup utilities posted from Carolina Code Camp.  Thanks for being patient with me.  I'm posting the 1st version of my utilities, these are the ones I use everyday to manage my systems at work.  I'm also working on refactoring them to cleanup my code and optimize the procedures and query plans as well as add new features and support for SQL 2008.  When I get the new stuff done I'll post it, so check back periodically.

Some of the basics on maintaining your database(s) are keeping the overall system running smoothly and running your maintenance in the correct order to get the most out of the maintenace.  Below is the order I would recommend maintenance.

1. History Cleanup
    - Remove old entries for backup history in MSDB
    - Removed old entries for database mail in MSDB
    - Cycle Error Logs for SQL and SQL Agent

2. Transaction Log backup before maintenance (Full Recovery Model - empties log file on database)
3. Check Database Integrity (Database, Allocation, Catalog, Table, Filegroup)
4. Defragment Database Files (Data, Log, file_id, file_name)
5. Rebuild HEAP on tables w/o Clustered Index (it is better to have a clustered index on every table)
6. Rebuild Clustered Indexes
7. Rebuild Non-Clustered Indexes
8. Update Usage (* only needed after upgrading from 2000 or on a 2000 database)
9. Update Statistics (All, Column, Index)
10. Transaction Log backup (Full Recovery Model - before backup to empty log file)
11. Full Database Backup

To understand the reasons for doing maintenance on the database, take a look at my presentation.  I have included a section on the Database File Architecture.  As the database is used and records are added, deleted and updated, it causes the database and indexes to fragment just as the hard drive on your computer does.  The data files may seem like just a container that holds data but they are far more complex than that and contain a lot of parts that need maintaining.  If you have any questions, feel free to shoot me an email and I'll do my best to help you.

A link to my presentation, utilities and documentation is below.  Here is a list of the utilities I have in my database copy and this version is for SQL 2005, I have another version of the database for SQL 2000 (email me if you need it).  I put them in a database because it was easier to deploy them that way and I wanted to keep them off master since in the future I plan on adding more functionality.

CHECKDB
DEFRAGDB
REINDEX
STATISTICS
BACKUPS
Backup History Cleanup

Database Mail Cleanup
Clear Proc Cache Adhoc
Alert Broken Log Chain

 *** Update defaults for procedures to suit your environment ***

Note:
As with any code that you are not familiar with, review the source code before using it.  The last thing you would want to do is execute something you never checked against your production environment and find out that somebody slipped something like a DROP DATABASE command in there.

This code has been tested and is supplied to you without warranty and as is.

Known issues:
 - Case-sensitive collation: there are minor bugs related to my variable names and table names that I haven't fixed in this version that will only cause an issue on a case-sensitive collation.

- Alter Index (Rebuild Indexes Online), Enterprise version of SQL 2005: I need to correct my logic for online reindexing, it does not currently support online reindexing on Enterprise version.





There are also some other individuals who have created some great utilities for maintenance as well.  My utilities work well for me but they are not the only solution out there, go check out Ola Hallagren and Tara Kizer's maintenance solutions.  The funny thing is that we have all written our utitlies independently and there are strong similarities between them, I guess it comes down to there are only so many ways to boil water.

Ola Hallagren
http://ola.hallengren.com/

Tara Kizer
http://weblogs.sqlteam.com/tarad/Default.aspx