I spoke at Oklahoma City 8/27/2016. This is first visit at Oklahoma City.
It was an awesome experience.
You can download my presentation at here.
Posted by Simon Cho on 08/27/2016
I spoke at Oklahoma City 8/27/2016. This is first visit at Oklahoma City.
It was an awesome experience.
You can download my presentation at here.
Posted in Common | Tagged: #553, Minimal Logging, SQL Saturday | Leave a Comment »
Posted by Simon Cho on 08/01/2016
https://technet.microsoft.com/en-us/library/ms190692.aspx
Compared to the full recovery model, which fully logs all transactions, the bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions. The bulk-logged recovery model protects against media failure and, for bulk operations, provides the best performance and least log space usage.
However, the bulk-logged recovery model increases the risk of data loss for these bulk-copy operations, because bulk logging operations prevents recapturing changes on a transaction-by-transaction basis. If a log backup contains any bulk-logged operations, you cannot restore to a point-in-time within that log backup; you can restore only the whole log backup.
Solution : We can run Log backup more frequently during a minimal logging operation.
https://technet.microsoft.com/en-us/library/ms190203(v=sql.105).aspx
For a database that uses full recovery, switching to the bulk-logged recovery model temporarily for bulk operations improves performance. However, point-in-time recovery is not possible with bulk-logged model. Therefore, if you run transactions under the bulk-logged recovery model that might require a transaction log restore, these transactions could be exposed to data loss. To maximize data recoverability in a disaster-recovery scenario, we recommend that you switch to the bulk-logged recovery model only under the following conditions:
If you satisfy these two conditions, you will not be exposed to any data loss while restoring a transaction log that was backed up under the bulk-logged recovery model.
We recommend that:
Posted in Common | Tagged: Bulk recovery, Minimal Logging, Point-in-time recovery | Leave a Comment »