Do you have a SQL Maintenance plan setup for your SQL Servers? When was the last time you checked to see how they were configured?

Not all maintenance plans are created equal. Just backing up your databases is not enough. You need to make sure that the integrity of the database is being checked on a regular basis.

Otherwise, you may be unable to restore from backup if the database had corruption at the time the backup was taken. SQL will faithfully let a backup complete successfully without checking the integrity of the backup, as that job is left for DBCC CHECKDB. If there is corruption in the database, you want to know about it right away so that you can restore from a clean backup. Otherwise, you’ll be simply making corrupted copies of the database in backup form that cannot be used to restore the database.

Here is an error message you might see if you attempt to restore a database that was corrupt before the backup was taken.

image

In the case of corruption in a stat blob, the corruption is in the sysindex system table, so DBCC will not repair it. In the case that I encountered, the corruption could not be repaired. The problem was actually much worse, because the Maintenance plan had not been checking for database integrity, so all the backups had the corruption as well.

image

So to prevent data loss like this from happening, check your maintenance plans and make sure they perform an integrity check on the database. Otherwise you may find yourself with a useless backup.

See also http://support.microsoft.com/kb/932744

 

Key points for configuring a SQL Server Database Maintenance Plan:

  • If your Databases are in full recovery mode, you need a separate maintenance plan to truncate your log backups, otherwise the size of your LDF files will grow forever.
  • A maintenance plan should include either index reorganization or index rebuilding; not both. If you are running a version of SQL older than 2005 SP2, do not choose rebuild. See KB 932744.
  • Update statistics is not necessary because the SharePoint Timer service performs this task automatically
  • Always begin with the database integrity check. If the integrity check fails, do not perform the remaining tasks. Instead, repair the suspect database.You can safely check all SharePoint Products and Technologies databases for integrity.

clip_image010

To maintain a sustainable and stable SharePoint Products and Technologies deployment, you should only shrink content databases because deletion are most common in these databases, Shrinking the configuration database, Central Administration content database, SSP databases, and Search databases is unnecessary and can lead to fragmentation.

clip_image012

In the Shrink database when it grows beyond field, type the size in MB at which a database should be a candidate for shrinking.

Set this value to 20% more than the maximum size you want your content databases to grow. For example, if you have established a database architecture that allows up to 100 GB for each content database, set this value to 120 GB.

In the Amount of free space to remain after shrink box, type the percentage of free space to retain in each database after the shrink operation.

We recommend that you specify 10% for this value. Setting this value can help reduce fragmentation when you have scheduled frequent shrinking.

Select either Retain freed space in database files or Return freed space to operating system, and then click Next.

We recommend that you return the freed space to the operating system to reduce instances of failures in database expansion, creation of new databases, and search and indexing operations.

On the Define Rebuild Index Task page, in the Databases list, specify the databases to reorganize the indexes for.

18. Select Change free space per page percentage, type 70, and then click Next.

Change free space per percentage sets the fill factor for the database.

clip_image016

19. On the Define Maintenance Cleanup Task page, set the values that meet your needs, and then click Next.

We recommend that you delete Maintenance Plan text reports.

clip_image018

20. On the Select Report Options page, select Write a report to a text file, select a location for the files, and then click Next until the wizard is completed.

clip_image020

Summary

Whichever method you choose to use, consistently maintaining the databases that host SharePoint Products and Technologies can significantly improve the health and performance of your system.

Ensure that you have reliable backups for all databases before you implement maintenance operations and maintenance plans.

Before you implement consistently running maintenance operations or a maintenance plan, test the impact of the operations on your system, and the time required to run them.

As much as possible, set any maintenance operations or maintenance plans to run during off hours to minimize the performance impact to users

This information is from Bill Baer’s excellent white paper.