Minimal Logging

The database is in Simple recovery mode.

When I use the following,

truncate table testtable

insert into testtable with(tablock)

select * from testtable1

My ldf file grows big. This means that this is not following the minimal logging. But if I wrap truncate and insert into with begin and end separately the minimal logging happens,

begin

truncate table testtable

end

begin

insert into testtable with(tablock)

select * from testtable1

end

What is the reason behind this ?

January 8th, 2014 8:24pm

Always state what version are you using.

http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/minimal-logging-changes-in-sql-server-2008-part-2.aspx   

Free Windows Admin Tool Kit Click here and download it now
January 9th, 2014 1:07am

Hello,

I will try to reproduce the issue and revert but I am sure Truncate command does not have full logging and does not create huge transaction log.

Regarding putting in Begin tran i think its just way or making the statement atomic.Either complete or rollback.

How do you test its truncate creating huge logs?

Also as Uri said please state version

select @@v

January 9th, 2014 1:15am

i think below article would help you to clear your doubt.

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1930-truncate-table-is-non-logged/

Free Windows Admin Tool Kit Click here and download it now
January 9th, 2014 1:33am

Hello,

>>My ldf file grows big. This means that this is not following the minimal logging. But if I wrap truncate and insert into with begin and end separately the minimal logging happens,

For just Truncate statement  to just prove that if truncate is between begin and end tran will logs generated be more.No in both case same logs will be generated.

I took help of DMV sys.dm_database_transaction to see how much logs are generated for this transaction

Now with truncate between begin and end command.

You can see same logs generated.


January 9th, 2014 3:16am

Now for Insert Operation same logs will be generated.

Now for insert operation also logs generated will be same


Now you need to find out why your log grew so much ,i guess there must be some other transactions running which is causing log to grow.

Truncate is always minimally logged in all versions of SQL server.I tried this on Adventureworks2008R2 database

Free Windows Admin Tool Kit Click here and download it now
January 9th, 2014 3:17am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics