WSS_Logging Database Recovery Model

Question

I'm looking for best practice advice when setting the SQL recovery model for the WSS_Logging database in SharePoint Foundation 2010. We currently have this database set to use the "Simple" recovery model.

Other SharePoint databases (WSS_Content, WSS_SharePointConfig) are currently using the "Full" recovery model which gives us point-in-time restore capability.

Should we be using the "Full" recovery model on the WSS_Logging database as well? Why or why not?

Answer 1

It comes down to whether or not you'd need to restore that data to a specific point in time, and I'm really having a hard time understanding a situation where you'd need to do that, which means that Simple the the way to go.

I think there's a couple of things here for you to consider to see if you would need that capability:

1. It's important to remember what's in this database: logging information from your farm. The most relevant data in this is data from your ULS logs. This is relevant for a few of reasons: A) if you have to do a restore, I would expect that you wouldn't want to roll this data back b/c you might lose information about what forced you to need to do the restore in the first place and impede your ability to do root cause analysis; and B) the ULS data is also stored locally on your server, so there's going to be another source for that data should you lose the database.
2. It's also important to think about how you're going to recover your farm and its databases. In most cases, its going to be much easier to rebuild service applications for the new farm than to restore the old ones, unless you've done a ton of undocumented configuration of those applications that you can't recreate. In general, I haven't seen a lot of people get in and get real tweaky with the Usage and Health Data Collection service application that the WSS_Logging database is associated with. Because of that, I think it would be much better to build a new Usage and Health Data Collection service application for the new farm than it would be to try and restore the old one.

Does that help?

John

Answer 2

The purpose of the Full recovery model is to enable you to recover your database after a failure. Full recovery retains every database transaction in the transaction log file (usually the .ldf file), meaning that you can usually use the "tail of the log", in addition to your database backups, to recover your data up to the point of failure, or to a specific point in time. With the Simple recovery model, you will only be able to recover your data to the point of your last (or earlier) backup.

Using the Full recovery model comes at a cost in compute resources. Since you can probably live without your logging data, and given that there will be a lot of transactions, it is probably a good idea to use the Simple recovery model for this database.

On a related point, if you use the Full recovery model, and don't back up your database, the transaction log file grows and grows. This is the cause of the "huge WSS_Config database transaction log file" question that crops up from time to time.

For more information about SQL Server, I recommend the SQL Skills site.