Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer
    
Perfect John!! That's the information I was looking for. Thanks!! –  pmartin Nov 15 '11 at 20:30
add comment

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.

share|improve this answer
    
Thanks for the reply but most of that information does not address my question. I fully understand the differences between Simple and Full recovery in MS SQL Server. I'm trying to understand whether the WSS_Logging database should be using the Simple or Full recovery model. I'm not sure what type of information is stored in this database or how often the DB is written to. If our SP environment crashes and we restore the content/config DB's to a specific point in time but only restore the Logging DB to the last full backup - is that OK? –  pmartin Nov 15 '11 at 19:40
    
As I said in my answer, you will probably want to use the Simple recovery model for your logging database, for the reasons given. It is write-heavy, and it is not critical to your farm. –  SPDoctor Nov 16 '11 at 12:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.