Note:- Please refer to post mentioned here to see why there are less number of log records. Actully this is not minimal logging but its less number of log records/
As per BOL ( here and here), minimal logging is only possible in SIMPLE and BULK-LOGGED recovery mode using bulk operations and FULL recovery mode fully logs all transactions.
But, I have seen that the minimal logging is happening for when we load data into tables using SSIS, for some combination minimal logging is happening even if database in FULL recovery mode. Below is the create to create such database and source table.
use master
go
if db_id('minimalLogTest') is not null
begin
alter database minimalLogTest set single_user with rollback immediate
drop database minimalLogTest
end
go
Create database minimalLogTest
go
Alter database minimalLogTest set recovery FULL
go
use minimalLogTest
go
if object_id('sourceTable') is not null
drop table sourceTable
go
Create table sourceTable
(
id int identity(1,1),
name nvarchar(10) default 'abc',
dateOfbirth date default '20000101'
)
go
--now insert 1000000 rows into source table
declare @i int = 0
Set nocount on;
begin transaction
while ( @i < 1000000)
begin
insert into sourceTable default values
select @i = @i + 1
end
commit
--now take a full backup to change database really into FULL recovery mode
Backup database minimalLogTest to disk =N'c:\minimalLogTest.bak'
go
if object_id('targetHeapTable') is not null
drop table targetHeapTable
go
Create table targetHeapTable
(
id int ,
name nvarchar(10) ,
dateOfbirth date
)
go
if object_id('targetTable') is not null
drop table targetTable
go
Create table targetTable
(
id int ,
name nvarchar(10) ,
dateOfbirth date
)
go
Create clustered index ix_targetTable_id on targetTable(id)
go
I haved used below queries to track number of log records generated
select AllocUnitName,* from fn_dblog(NULL,NULL) where AllocUnitName like '%targetHeapTable'
go
select AllocUnitName,* from fn_dblog(NULL,NULL) where AllocUnitName like '%targetTable%'
In this test, I have tested for following things
- OLEDB destination (with FASTLOAD) for HEAP without any indexes
- OLEDB destination (with FASTLOAD) for table with Clustered Index
- SQL server destination for HEAP without any indexes
- SQL server destination for table with Clustered Index
- SQL server destination for non-empty table with Clustered Index
Same package is attached below.
Between each test, I had taken log backups so that count of log entries is manageable.
First test is to start with HEAP. Before loading, if we run below query no rows is returned
select AllocUnitName,* from fn_dblog(NULL,NULL) where AllocUnitName like '%targetHeapTable'
--Zero rows
Now, start loading data

Now, when we run below queries..
select COUNT(*) from fn_dblog(NULL,NULL) where AllocUnitName like '%targetHeapTable'
--4622 rows
select COUNT(*) from targetHeapTable
--1000000 rows
Even if we look into detailed records, we found entries simpler to what we got in bulk operations in SIMPLE and Bulk-Logged recovery mode.

Even if run below query, we didn’t get any rows.
select * from fn_dblog(NULL,NULL) where AllocUnitName like '%targetHeapTable' and Operation like 'LOP_INSERT_ROWS'
So, looking at these details, we can conclude that minimal logging operation is happening even if database id in FULL recovery mode.
Same way even if you run the task again which inserts data into HEAP using OLEDB FastLoad, you can find that minimal logging is happening with each load.
I run these tests for all other combinations and found out below detail:
| Combination |
Minimally logged |
| OLEDB destination (with FASTLOAD) for HEAP without any indexes |
YES |
| OLEDB destination (with FASTLOAD) for table with Clustered Index |
NO |
| SQL server destination for HEAP without any indexes |
YES |
| SQL server destination for empty table with Clustered Index |
YES |
| SQL server destination for non-empty table with Clustered Index |
NO |
Note:- I have not considered scenarios while backup is running or tables involved in replication
This can be helpful in scenario, where want to load data and not want to switch between recovery modes (al though, as its not confiremed by Microcost, this behaviour can be changed in future releases). Only thing to take note here is Point-in-time restore will not work in that log backup.