Maintaining your MOSS 2007 SQL Databases

  • Article

If you're running Microsoft Office SharePoint Server 2007 (MOSS 2007), your're also running a SQL Server, hopefully the 2005 version to get the best performance. MOSS will be creating all your databases for your, so you might thing you should not worry about maintaining those databases, right? Not really...

Databases need proper care and, if you're doing anything significant with MOSS, you should have a good Database Admin on your staff. If you just install SQL, let MOSS create the databases for you and do not take any care of them, you will likely end up with disk full error on your database server...

Let me explain. When you create a database in SQL you also create a log file. These log files are part of how SQL writes information to the disk. The server will keep growing those log files and this is great if you need to do incremental backups or if you need to do a point-in-time restore. However, without proper care, these log files will grow continually and will eventually fill up your disk, no matter how big it is.

To keep those log files within a manageable size you either need to switch your database to a simple recovery model (where the log is automatically truncated, but you can't use logs in your backup scheme) or you keep the full recovery model but you do regular log backups (the log is truncated after you back it up).

A single-server, all-features-enabled MOSS setup will typically create at least 8 databases: Configuration, SSP, WSS Search, MOSS Search and 4 content databases (central admin, SSP, My Sites, main web application). The SSP, WSS Search and MOSS Search databases will be set to a simple recovery model. However, by default, the configuration and content databases will use a full recovery model. To check on the databases and the recovery models they use, try this query on the SQL Server Management Studio :

USE Master
SELECT Name, Recovery_Model_Desc FROM Sys.Databases

If you just want your server not to keep the logs without doing any backups, use this command for every database that reports a full recover model:

ALTER DATABASE [name] SET RECOVERY SIMPLE WITH NO_WAIT
GO

Note that, by doing so, you are disabling SQL's ability to perform log backups and you won't be able to do point-in-time database restores later on if you need them. However, if this is a test server and you're not doing regular backups, this command will prevent your log file from growing. You can also find the recovery model in the SQL Server Management Studio GUI by right-clicking on the database and checking the database options.

Even with a simple recovery model, you still do need to take care of your databases. One simple thing you can do is to create a Maintenance Plan, which will include all the main things you need to do to keep your database running. Here's how you would create one:

  • Open the SQL Server Management Studio
  • Connect to the SQL Server
  • Navigate to Management / Maintenance Plans
  • Right click Maintenance Plans and select Maintenance Plan Wizard (Name, Description, Server, Auth), Click "Next"
  • Check "Check Database Integrity", "Reorganize Index", "Update Statistics", "Clean Up History" and "Backup Database (Full)", Click "Next"
  • On the "Define Check Database Task" window, Select "All user databases", Check "include indexes", click "Next"
  • On the "Define Database Integrity Task" window, Select "All user databases", check "compact large objects", click "Next"
  • On the "Define Update Statistics Task" window, Select "All user databases", select "All existings statistics", click "Next"
  • On the "Define Backup Database Task" window, Select "All user databases", select "create a sub-directory for each database", specify a folder, click "Next"
  • On the "Define Cleanup History Task" window, click "Next"
  • On the "Select plan properties" window, define an adequate schedule, click "Next"
  • On the "Select report options" window, specify a folder for the logs, click "Next"
  • Click "Finish"

After this, you can wait for the scheduled time or just right-click the newly created Maintenance Plan to execute it immediately. Please note that your need to have the SQL Server Agent running and that the execution of all these tasks can take some time (at least a few minutes on small databases).

Keep in mind that the best option of all is to have a real Database Administrator around and design the best maintenance and backup scheme for your specific scenario, which might include using the full recovery model and regularly scheduled database and log backups.