Maintenance Plan in SQL Server 2005

Case
Sometimes things just won't be “so plain to get” as Microsoft thinks it is. I was working at a customer with no database administrator. They needed to clean up there log-file to create free disk space and to create a new back-up every day. So I started browsing about maintenance plans and found a good source.

i The Maintenance Plan Wizard in SQL Server 2005 enables administrators to perform the following maintenance tasks against SharePoint databases:

• Check database integrity
• Reduce a database
• Reorganize an index
• Clean up the history
• Update statistics
• Rebuild an index

We have tested these tasks and the effects that these tasks have on database schema and performance. The following table summarizes the results of these tests.

Task -> Safe to perform this task?

• Check database -> Yes
• Reduce a database -> Yes
• Reorganize an index -> Yes
• Clean up the history -> Yes
• Update statistics -> Yes. However, this task is unnecessary because the SharePoint Timer service performs this task automatically.
• Rebuild an index -> No. The task does not restore existing index options before the rebuild operation. However, you can use scripts that restore index options. Note This problem was corrected in SQL Server 2005 Service Pack 2.

Source: http://support.microsoft.com/kb/932744

Problem
This is the point where Microsoft (or maybe just me?) thinks that things are so easy. I created a new Plan and created all of the below objects:

• Check database integrity
• Create a Full back up
• Reduce a database (should release the log file disk space)
• Update the statistics
• Clean up the history

Problem was…? Somehow the log file entries just wouldn’t be deleted during the shrink task, so no disk space was released! Am I wrong thinking that a Full back up would make it possible to delete log file entries? (You can leave a message at the end of this blog post ;-)

Solution
There was no other option… I asked a database colleague for some tips. With those tips I thought out a plan and created the following maintenance plan.

Now everything works like a charm!!!