Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
riya.johnson
Starting Member
17 Posts |
 Posted - 2009-09-07 : 06:02:19
|
| Log Shipping is configured in SQL 2005. Recovery model is FULL. Weekdays it goes fine. On Saturday, there is a maintenance activity (index rebuilding). Due to this the log file size increases to the extent that log shipping fails. So every weekend after this activity, log shipping has to be reset manually.My query is,If we change the recovery model to Bulk-logged just before the weekend index maintenanace activity & revert back to full recvery model after this acivity, the size of the log file will not be very large. So probably log shipping will go fine. As per my knowledge, log shipping will not get affected due this changing of recovery models. Correct me if i am wrong. Also please tell me if this will have any adverse effect on the database?Riya Johnson |
|
|
shah429
Yak Posting Veteran
52 Posts |
Posted - 2009-09-07 : 16:19:16
|
| Log Shipping does support the Bulk-logged recovery model(it will not break log hipping process), however, please note that some operations which are not fully logged(for example rebuild index etc) will not shipped to the stand by server. So you basically have two options IMO:1. Change the recovery model to Bulk-logged, consequences are listed above.2. Let the recovery model be FULL, make frequent transaction log backups during the maintenance operation. This will be better if it works. Let us know. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-09-15 : 01:42:23
|
| All operations will be shipped to the remote server in bulk-logged recovery. What happens is that all that's logged is that certain extents have changed, then when the log backup runs those changed extents are included in the log backup.Bulk-logged can reduce the growth of a transaction log, but in general the log backups are as large, or larger, than in full recovery because the changed extents are part of the log backup.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Changing recovery models in Log shipping