Freigeben über

Minimal Logging changes in SQL Server 2008

  • Artikel

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 

Comments

  • Anonymous
    March 24, 2008
    In the previous example http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/06/minimal-logging-changes-in-sql-server-2008-part-1.aspx

  • Anonymous
    May 20, 2008
    Given some of the &quot;larger&quot; features being introduced with Sql 2008 which are getting lots of

  • Anonymous
    May 20, 2008
    Katmai (Sql 2008) - Transaction Logging Enhancements

  • Anonymous
    August 11, 2008
    As part of SQL2008 release, you can get minimal logging when bulk importing into a HEAP using the following

  • Anonymous
    March 15, 2009
    Thank you for useful post. I'm going to post it to my blog by Korean ^^

  • Anonymous
    March 16, 2009
    Here it it, http://blog.naver.com/visualdb/50044303365 Thanks! ^^

  • Anonymous
    September 06, 2013
    Nice and informative article

  • Anonymous
    July 18, 2015
    "Please refer to the earlier post" -- a link would have been nice, it's quite difficult to find something just by it being "earlier"