A blog by Jose Barreto, a member of the File Server team at Microsoft.
All messages posted to this blog are provided "AS IS" with no warranties, and confer no rights.
Information on unreleased products are subject to change without notice.
Dates related to unreleased products are estimates and are subject to change without notice.
The content of this site are personal opinions and might not represent the Microsoft Corporation view.
The information contained in this blog represents my view on the issues discussed as of the date of publication.
You should not consider older, out-of-date posts to reflect my current thoughts and opinions.
© Copyright 2004-2012 by Jose Barreto. All rights reserved.
Follow @josebarreto on Twitter for updates on new blog posts.
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 MasterSELECT 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_WAITGO
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:
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.
Lots of homework this week...Great post on How to maintain your MOSS 2007 databases on Jose Barreto's...
This is a follow-on to Does your organization need a DBA? and Andy Leonard's Database Professionals:...