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

Code Camp Presentation info coming soon...

To all who saw my presentation at Code Camp on Automating SQL Maintenance and Backups today or want to see it. I will try to get the presentation and code posted within the next couple of days, just need to organize it and write up some additional info for you. Check back soon...

Thanks,

Jeff

Carolina Code Camp

Carolina Code Camp is coming up quickly! It will be on October 10th, 2009 at CPCC - Levine Campus in Matthews, NC. More information can be found on the website:
http://codecamp.developersguild.org/

We are still accepting speakers for the Carolina Code Camp. We will have the traditional Code Camp stuff for developers and we also have a SQL Server Track with a focus on the topics that interest the development community.

Call for SPEAKERS...If you've been wanting to speak or looking for a venue to speak at about .NET, SQL Server, ALT.NET, Project Planning, Mobile App Dev, iPhone App Dev and anything else on technology that you think an audience would like to hear about. Now is your opportunity to speak to your peers.

If you're interested in attending Carolina Code Camp, the event is free. Just stay tuned to the website and registration will be opening soon...
http://codecamp.developersguild.org/

Jeff

Carolina Mega Code Camp Announced...

Save the Date! The Carolina Mega Code Camp has been announced to be held on Saturday, October 10, 2009 at the Levine Campus of CPCC in Matthews, NC. This event is also known as Charlotte Code Camp.

"Developing for Dollars, our year-long focus on providing you with the skills to create successful applications and connecting you with the right organizations to make money from your work, continues. We have invited every .NET user group in the Carolinas to participate in this Code Camp. Never before have so many groups come together to present one single event. So mark your calendar and plan to join us on Saturday, October 10 at the Levine Campus of CPCC for the Carolina Mega Code Camp! More event details coming soon."

See the Charlotte Developers Guild website for more information:
http://www.developersguild.org

New New Blog...

I'm finally getting my new blog setup after urging from friends and colleagues to start blogging again. I'll have some new posts coming up soon on SQL Server optimization tweaks and some maintenance stuff I've been working on.

Keep an eye out for the Charlotte Mega Code Camp, it will be on October 10, 2009 and I'm working on a presentation or two to give at that. The event will be at the CPCC Levine Campus in Matthews, NC. When I finalize what I'll be speaking about I'll post some more info. Stay tuned for details and check out the Charlotte Enterprise Developers Guild website for info on the event. Charlotte Enterprise Developers Guild