Minimal Logging changes in SQL Server 2008
Published Mar 23 2019 05:15 AM 747 Views
Microsoft
First published on MSDN on Mar 06, 2008

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 when data is loaded using TABLOCK as shown in the example below


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







Version history
Last update:
‎Mar 23 2019 05:15 AM
Updated by: