Switching Configuration database Full to Simple recovery mode

Question

Hi,

My sharepoint configuration database is set to FULL Recovery mode. Now transction log size is around 100 GB.
I am also using Database mirroring just for content database not for configuration database.

Through reading found take log backup after 15 0r 20 min to keep log small. Second option is to set Configuration database
in simple recovery model.

Please guide me as i am production environment.

Changing configuration database from full to recover needs and downtime, would it be any impact on production. give suggestions.
reference link : http://technet.microsoft.com/en-us/library/cc678868.aspx

in order to reclaim log file size before setting simple recovery mode which option / steps to follow.

Please guide me have to perform steps in production environment.

Thanks.


 

---

iffi

Answer 1

Dear imughal,

You should contact your Sr. DBA for this. Switching the recovery model from Full to Simple will break the log chain.

A database in Simple Recovery Model can be recovered till the last available full backup.

By initiating frequent transaction log backup, you can make space available for the upcoming transctions and your transaction log size will not grow; however, it can grow, if there were active transactions (for database in Simple/Full recovery model).

If your database is in Full recovery model, you should initiate transaction log backup.

Could you please provide the output of the below command:

USE [master];
GO
select log_reuse_wait_desc from sys.databases where database_id = db_id('TestDB');
GO

---

SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

Answer 2

No downtime needed. But do consider recovery objectives before you switch to simple. Details: http://www.karaszi.com/SQLServer/info_large_transaction_log_file.asp

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 3

Sudeepta,

result of above query is LOG_BACKUP. its means take log backup correct me if i wrong. 

we take farm full back up on every Sunday and Differential backup daily.

For database take full back after 2 week and differential daily. 

What if i set configuration database to simple would i be able to restore from my last full back and last differential back. for example i have Sunday full back and Wednesday Differential back.  if failure occur on Thursday, Than what would be my Recovery Point Objective.

Sunday full backup till Wednesday Differential   OR just till Sunday Full Backup.

Please guide.

 

---

iffi

Answer 4

result of above query is LOG_BACKUP. its means take log backup correct me if i wrong. Yes, you are correct.

i have Sunday full back and Wednesday Differential back.  if failure occur on Thursday, Than what would be my Recovery Point Objective.

Ans:You can recover data till Wednesday diff backup (Sunday Full Backup + Wednesday Diff backup). Any data after the Wednesday differential backup will be lost. If this is ok for your environment, then you can switch to Simple Recovery Model. The transaction log file can grow that large in Simple recovery model; however once all the active transactions are committed, SQL server can initiate a checkpoint, which can remove the inactive portion of the t-lg file, keep the file size to the initial file size.

The only way to reclaim the transaction log space, while a database is in Full Recovery model, is by initiating Transaction Log Backup. You should perform frequent log backup, so that the inactive portion of transaction log file can be reused.

I think, it's worth to read the following articles:

http://technet.microsoft.com/en-us/library/ms179355(v=sql.105)

http://technet.microsoft.com/en-us/library/ms180892(v=sql.105)

http://technet.microsoft.com/en-us/library/ms189573(v=sql.105)

---

SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

Answer 5

thanks.

is OK with us to have (Sunday Full Backup + Wednesday Diff backup)  at restore time. what would be your suggestion / recommendation with respect to SharePoint configuration database.

if i backup transaction and  truncate it. Than schedule log backup after every 10 min what would be load on SQL server to run this job on every 10 min. will it keep size small. 

or should i set recovery model to simple. if change what should i have to monitor and take care. 

thanks.

---

iffi

Answer 6

Sorry, I don't know Sharepoint, and a suggestion on that will be difficult.

About the log backup, again you can schedule it for every 2/4 hours if required. I think, we should wait for Other Community Experts feedback.

---

SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

Answer 7

Only you can determine what recovery model you shoudl use. What is your recovery point objective? I.e., how much data can you afford to lose?

One day? Then stick with db and diff and use simple recovery model.

One hour? 10 minutes? If so, schedule a log backup accordingly and use full recovery model. The load if doing a low backup is low.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 8

We manage a large SharePoint 2007 farm with about 80,000 users (~10k concurrent) and have the MOSS Config database in FULL recovery. I can't speak for SharePoint 2010 but in MOSS 2007 that configuration database is absolutely critical and you should take every precaution to protect the data and be able to do a point in time restore if there is a failure. Other databases are a bit easier to recover from a failure but the configuration database is critical and you really cannot afford anything but a point in time restore to the time of failure.

A couple of comments on the discussion here:

1. If you are in full recovery and are running 15 minute transaction log backups with one Wednesday differential, you will have around 96 transaction logs for each day. So if you have a failure shortly before your differential you will have to reconstruct a log chain with three hundred or so transaction log backups, which is not a situation you want to be in. At the very least you should consider running a nightly differential so that the most transaction logs you would have to restore would be 96. On our environment, we run weekly fulls + nightly diff + hourly log, so a point in time restore (which we test often) is to restore one full, one diff, and up to twelve log backups.

2. If you have the ability to use Backup Compression on the log backups, do it. You will signficantly lessen the impact on the network, although there is a CPU cost.

3. Look into VLF fragmentation by running DBCC LOGINFO in the context of the MOSS Config database. If more than a hundred or so rows are returned, you have fragmentation in your log file and should address that as part of your strategy to manage the log. To "defragment" the log file (during a maintenance window), run multiple transaction log backups in a row and then shrink the file down to the smallest it will go, around half a MB or so. Then size it out to about 25% larger than the biggest you ever expect it to be with your log backup schedule. Then run DBCC LOGINFO again and you'll see that the number of lines returned is much lower. You should really only have to do this once and then avoid shrinking your log file at all.

Hope this helps.

Matt

---

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you.

Answer 9

It is worth repeating what TiborK has already mentioned: know your recovery objectives and SLAs. I would assume you are using a combination of site collection backups and database backups to achieve these objectives.

A couple of things to consider from the database perspective

1. If you switch your content databases from FULL recovery model to SIMPLE, your database mirroring configuration will break as it requires FULL recovery model http://msdn.microsoft.com/en-us/library/ms177412(v=sql.105).aspx Changing recovery models will not require downtime
2. If you have database mirroring configured for your content databases, make sure you have regular LOG backups to avoid the log file bloat. LOG backup every 4 hours can be a good start and measure the backup size accordingly to see if you need to change the frequency
3. Keep your VLFs as low as possible. High number of VLFs in the log file will affect recovery time in the mirror database. This would require shrinking your log file and resizing accordingly, depending on the size of your content database
4. Test changing your database mirroring configuration from SYNCHRONOUS to ASYNCHRONOUS and measure response time on the web front end servers. This, of course, will also be determined by your recovery objectives
5. Implement a custom index maintenance script for your SharePoint databases. A highly recommended solution is to implement this script from Ola Hallengren http://ola.hallengren.com/versions.html If you use the out-of-the-box index maintenance tasks in the Database Maintenance Plan, this will cause your database log files to grow and will affect your database mirroring configuration

From the web front end side,

1. Make sure you have your Recycle Bin feature enabled to address the %#$!*^ moments when end users accidentally delete contents
2. Schedule your site collection backups according to site criticality and importance. You don't want to have site collection backups running every night only to find out that it is going way beyond your maintenance window. In MOSS 2007, site collection backups lock the site that users will have problems accessing their corresponding site collections while the backup is running. This has been addressed in SharePoint 2010 with the -NoSiteLock option. However, you still need to create a backup strategy for your site collections according to your recovery objectives and SLAs
3. Test your site collections backups regularly

Why did I even bother highlighting considerations for both the content databases and site collections when you were asking about switching from FULL to SIMPLE recovery model? Because a simple action like that will affect your recovery objectives and SLAs big time.

Hope this helps.

---

Edwin Sarmiento SQL Server MVP
Blog | Twitter | LinkedIn

Answer 10

Hi,

Thanks for all, let me consider all point and recovery and SLA. 

---

iffi