Please refer to the earlier post for the background information on minimal logging. Starting with SQL Server 2008, the minimal logging has been enhanced. These enhancements are available to regular TSQL Insert as well. One of the key customer scenario was to be able to transfer data from a staging table to the target table. The only choice the customers had was to use SELECT * INTO <target> from <staging-table> if they wanted minimal logging. The limitation of this solution was that customers had no control on DDL aspect of the target table. WIth this new enhancement, this restriction is now removed.
I used a flavor of the following query to find log records
select top 10 operation,context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName
from fn_dblog(null, null)
where allocunitname='dbo.t_heap'
order by [Log Record Length] Desc
(1) Insert into a HEAP is minimally logged under TABLOCK but fully logged without TABLOCK. This is one of the features that customers have been asking for. By the way, the only drawback is that it holds X lock, unlike Bulk Insert which holds BU lock, so you cannot insert using multiple threads.
-- create the source table
create table t_source (c1 int, c2 int, c3 char (100), c4 char(1000))
go
declare @i int
select @i = 1
while (@i < 1000)
begin
insert into t_source values (@i, @i+10000, 'indexkey', 'hello')
select @i= @i + 1
end
-- create the target heap
create table t_heap (c1 int, c2 int, c3 char(100), c4 char(1000))
go
-- this is minimally logged.
-- LOCK: X lock on the table
-- this behavior is same even when ‘t_heap’ is not empty
begin tran
insert into t_heap with (TABLOCK) select * from t_source
-- here are the top-10 log records. You can see that it is minimally logged
-- this is fully logged
insert into t_heap select * from t_source
-- here are the top 10 log records. You can see that it is fully logged
Thanks
Sunil
Anonymous comments are disabled
About Sunil Agarwal
Sunil Agarwal is a Program Manager in the SQL Server Storage Engine Group at Microsoft. He is responsible for concurrency, indexes, tempdb, LOBS, supportability, and bulk import/export. Prior to joining Microsoft, Sunil worked at DEC, Sybase, BMC Software and DigitalThink, focusing primarily on core database engine technologies and related applications.
