Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

SQL Server 2017 Minimal Logging Not Behaving As Documented

According to my interpretation of the docs (https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd425070(v=sql.100)) the following scenarios should minimally log...

Recovery model must be Simple or Bulk Logged
Table must be either
A heap and insert done with TABLOCK
A heap + nonclustered index with TABLOCK and either trace flag 610 or SQL Server 2016+
A clustered index with no data and TABLOCK
A clustered index with no data and either trace flag 610 or SQL Server 2016+
A clustered index with data and trace flag 610 or SQL Server 2016+
A clustered index with nonclustered indexes and TABLOCK and trace flag 610 or SQL Server 2016+

On SQL Server 2017 the following scenarios fail to minimally log for me

A heap + nonclustered index with TABLOCK and either trace flag 610 or SQL Server 2016+
A clustered index with no data and either trace flag 610 or SQL Server 2016+
A clustered index with data and trace flag 610 or SQL Server 2016+
A clustered index with nonclustered indexes and TABLOCK and trace flag 610 or SQL Server 2016+

I've documented this more fully with examples here

https://gavindraper.com/2018/05/29/SQL-Server-Minimal-Logging-Inserts/

47 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Anonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

0 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base