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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-01-24 : 08:39:14
|
| David writes "Hi,We have several large databases (500GB+) that we log ship to provide redundancy and other uses. We have started doing extensive re-indexing jobs on these databases over night at the weekends, which has caused some rather large transaction logs (anywhere up to 15GB). The recovery model is set to Full at the moment, and usually this is fine during day to day activities. My question is: Can we set the recovery model to Bulk before the reindexing and reset back to full afterwards again to get these files reduced in size. Would this then speed up the restore process on the standby server - which currently takes anywhere up to 10-15 hours to apply these transaction logs. This obviously causes a bottleneck which means that the standby server gets out of date quite quickly, taking a day or two to catch up again. Are we going to miss any transactions by setting it to bulk instead of full? What other implications do we need to consider?Any help would be gratefully appreciated!Cheers." |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2006-01-26 : 01:27:56
|
| Yes, you can. However, while database log files aren't going to fill up during the reindex, log backups would still be pretty large as they will contain every page of the indexes you rebuild (similar to how a differential backup dumps out changed pages). So I am guessing you are still going fall behind on your log shipping. Reindexing itself should take less time under the bulk-logged model though. On the downside, you will lose the ability to perform a point-in-time recovery (when restoring log backups, taken under the bulk-logged recovery model, you will need to apply each backup in its entirety). Finally, you aren't gonna be able to back up the tail end of your database log if you lose a data file, even if the log file survives. |
 |
|
|
|
|
|
|
|
SQL Log Shipping and Recovery Models