Recently Microsoft released a white paper covering the Best Practices for Maintaining your SharePoint SQL Databases. This is frequently a topic that doesn’t get touched upon until SharePoint performance starts to degrade or when SQL Databases get to the size that they become unmanageable. Keep in mind that all content for your sites are contained in these databases so keeping them in tip-top shape should be high on the priority list.
The white paper starts off discussing the process of checking databases for errors and repairing those errors by using DBCC Database Console Command. For more details on the many functions of DBCC check out this MSDN Site. At first pass run this command to check for the errors. Read through the error report and understand the errors. Finally, run the command with the repair argument to repair any errors that were listed on the error report. Make sure you run these commands during off hours since it will degrade the performance of the farm.
The next section discusses file fragmentation on the SQL Databases. Then details how a few scripts can be created in order to reduce file fragmentation. Make sure to read though this section thoroughly since there are different operations for index and content databases. For those that are running SQL on Solid State Drives (SSD) you need not to worry, SSD’s will not fragment.
The last section details creating a SQL Maintenance Plan. The maintenance plan includes checking the databases for integrity, reorganizing the indexes, shrinking the databases and then running a clean up job. Of course the white paper describes this more in detail. One note, if you run all of these jobs in one maintenance plan this could take a lot of time and a lot or resources. I would suggest separating out the tasks if you cannot allow for a substantial maintenance plan.
This white paper is a must read for SharePoint and SQL Administrators.
