SQL Server 2008 has enhanced INSERT..SELECT statement to allow minimal logging in certain cases, documented here:http://msdn.microsoft.com/en-us/library/ms174335(v=sql.100).aspx
(See section: Best Practices for Bulk Loading Data)
We planned to use this feature in a situation where we wanted to insert millions of rows in an empty heap (or an empty clustered index) and made sure that all pre-requisites are fulfilled.a) The target table is a heap or an empty B-Tree,
b) TABLOCK is being used on the target table,
c) The database is in SIMPLE recovery model,
d) The target table is not used in replication.
The INSERT is done from a SELECT from a temp table (#temp) having around 300,000 rows (300,000 distinct Ids) joined with another table that has ~500 rows for each of the distinct #temp.Id. Thus, 500 x 300,000 = 150 million rows (around 10 GB)
We are on Microsoft SQL Server 2008 (SP1) - 10.0.2740.0 (X64).
But it seems the minimal logging is not working for us, as I see the transaction log growing to almost 10GB, during the insert if no other processes are running.
We also face transaction log full issue, when other processes are running along with this insert. (Transaction Log size = 20 GB)
There were no backups being run at the time of testing, which may have caused full logging.
Is the minimal logging using INSERT..SELECT available in all versions of SQL Server 2008 or did it get included in one of the Cumulative Updates?
I would really like to make use of this new feature in SQL Server 2008.
Any ideas on how to achieve it, would be appreciated.