Conditions for Server Minimal Logging in SQL Server

When doing minimal logging SQL Server logs only page/extent allocations instead of logging individual rows. For example, if N rows fit in a page, without minimal logging there will be N log records. With minimal logging there will be only 1 log record.

The following conditions need to be satisfied for minimal logging. -

1.       Database in Simple or Bulk Logged Recovery Model.

2.       Select INTO is Always Minimal Logged.

3.       For Other Bulk Import Methods  (INSERT INTO SELECT, BCP, BulkInsert ) the following additional conditions need to be satisfied.

·         TABLOCK must be specified on target table.

·         If the target table has indexes it must be empty.

·         If the table has no indexes it need not be empty.

Note: For a table with indexes, If you specify batch size for inserts (BCP/BulkInsert), only the first batch will be empty and will be minimally logged.