6

I have a primary database in FULL recovery mode which is part of Always On group. Is there a way to minimally log insert operation under FULL recovery model?

I have a process that is executed each day and insert few millions of records in a table. While the operations continued the transaction log file size is increased dramatically ( from 1 GB to 40 GB).

As I have read I can used some variations of INSERT which are not fully logging the operation but I am concern about the effect of switching the recovery model?

Improve this question
  • Thanks but Minimally logged operations are available only if your database is in bulk-logged or simple recovery mode. (technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx) and the database in full recovery mode. I am going to check the batches insert examples either. – gotqn Nov 11 '14 at 14:27
  • Could anyone tell if the numbers of records inserted matters, or the size of the row? I have 37 millions rows to insert, but I can group them in 147 772 rows if I change the table structure (for example XML and ID). The data will be the same as size (maybe bigger) but the rows will be less. – gotqn Nov 11 '14 at 14:32
8

No. Full recovery model is a prerequisite of AlwaysOn Availability groups as per the check list here, and minimally logged operations are only available under Simple or Bulk Logged recovery.

Quote from the Data Loading Performance Guide:

Minimally logged operations are available only if your database is in bulk-logged or simple recovery mode.

Re trace flag 610, what that added was the ability to do minimally logged inserts to tables with clustered indexes ( and/or non-clustered indexes ) with data already in them. This was not previously available.

The thing to bear in mind about minimally logged transactions as that as soon as a transaction log backup contains one of these, you lose the ability to do point-in-time restore with that backup. So hopefully now you understand why these are not available in Full recovery model

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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