question

gotqn avatar image
gotqn asked

Is there a way to minimally log insert under Always on?

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?
transaction-logalways-onlog-file-size
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
The recovery model has to be full for all the databases in an availability group. It's in the documentation. Break the insert into smaller chunks or use some other method that will help reduce the size of the transaction log.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.