Sql-server – logging in Bulk logged recovery VS Full recovery model

sql servertransaction-log

I am trying to understand the logging aspects in Full and Bulk recovery models.As per MSDN ,select * into test from abcsd is logged minimally in Bulk logged ,but in full recovery model,it is logged fully.But while reading transaction log i am seeing different behaviour,Can please some one shed some light on this.

Database recovery model:
enter image description here

TLOG:
enter image description here

From the above snip we can see even though DB is in Full recovery model,only extent allocations are logged.Is this expected.I am seeing the same behaviour for Bulk logged recovery model too with only change being database pages are also backed up.If only extents are logged in full recovery model,how point in time restore capability is acheived.Please let me know if i am missing any thing

Best Answer

I finally found the answer Red gate transaction log management book by Tony and Gail shaw.

Below is the Snip for a select into operation done in full recovery model:

enter image description here

From the above snip we can see all the operations done to sometable are logged under the context of lop_format_page and logrecordlength is also 8276 bytes which indicates SQl eventhough under full recovery model,doesnt log each and every row,rather it logs entire image of the page plus log headers.

Below is the Snip for the same operation done in Bulk logged recovery model: enter image description here

From the above Snip,we can see now there is no record indicating LOP_FORMAT_PAGE,rather Sql is now tracking only page allocations and extent allocations,plus some internal system table changes,but no data pages are present in the log.

Thanks to redgate for the amazing Book

Related Solutions

Sql-server – Why “Select * into targettable from sourcetable “ is faster than “insert into targettable select * from sourcetable

A couple of ideas/theories:

SELECT INTO... lets the RDBMS determine sort order based on order of your original table. If you insert into an existing table, there may be a sort needed to match a clustered or nonclustered index(es).

No Indexes - when you SELECT INTO... the RDBMS knows for certain there are no pre-existing indexes to update.

No Contention - since the table you are inserting into does not exist, SQL Server doesn't need to worry about row-level locking or contention handling. Nothing else can reference the table you create since it doesn't exist.

All that being said, there are other ways to insert into a table very quickly.

Make sure your clustered index keys match when possible. This means there is no on-the-fly sorting
Disable all non-clustered indexes. Self-explanatory.
Set recovery mode to simple and trace flag 610 to ON. Use the TABLOCK hint on your target table and NOLOCK hint on your source table.

For example, assume tablea and tableb have the same clustered index:

INSERT INTO TableB WITH (TABLOCK)
SELECT <Columns>
FROM TableA WITH (NOLOCK)

In my experience this is faster than using SELECT INTO... and then creating the clustered index afterwards. Please note this can also work on a table that already has data in it which is a much more useful scenario.

EDIT:

Here's a fantastically detailed whitepaper from MS for data load performance in Sql Server 2008.

Sql-server – Is it good idea to change Simple Recover Model into Bulk-Logged on ETL system

If you log grows on ETL in simple mode it means your inserts are not minimally logged. The requirements for minimal logging are described in Operations That Can Be Minimally Logged and Prerequisites for Minimal Logging in Bulk Import:

Minimal logging requires that the target [of a INSERT BULK operation] table meets the following conditions:

The table is not being replicated.

Table locking is specified (using TABLOCK).

The Data Loading Performance Guide also mentions the TF 610 effects and how it affects minimal logging.

Best Answer

Related Solutions

Sql-server – Why “Select * into targettable from sourcetable “ is faster than “insert into targettable select * from sourcetable

Sql-server – Is it good idea to change Simple Recover Model into Bulk-Logged on ETL system

Related Question