bulk logged insertion

Question

bulk logged insertion

VastSQL

SSC-Dedicated

Points: 36097
More actions
August 11, 2009 at 12:13 am

#132642

Hi Experts,
1)What happens when i insert just ten records in bulk log recovery model will it get logged?
2)If i insert 10000 records what happens in bulk logged recovery model?
3)If the first(question) gets logged then whats the difference in that? If the first(question) gets no logged then how can i recover the data?
TIA

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

From BOL:

Under the bulk-logged recovery model, if a log backup covers any bulk operations, the log backup contains both log records and the data pages that were changed by bulk operations. This is necessary to capture the results of the bulk-logged operations. The incorporated data extents can make a log backup very large. Additionally, backing up the log requires access to the data files that contain the bulk-logged transactions. If any affected database file is inaccessible, the transaction log cannot be backed up and all operations committed in that log are lost.
To track the data pages, a log backup operation relies on a bulk-changes bitmap page that contains a bit for every extent. For each extent updated by a bulk-logged operation since the last log backup, the bit is set to 1 in the bitmap. The data extents are copied into the log followed by the log data.

This means that operations that can be minimally logged don't write to the transaction log, but mark modified extents with a flag.

In full recovery model all data modifications get logged to the transaction log, which is slower for large data changes.

Hope this helps

Gianluca

-- Gianluca Sartori

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

Thanks Gianluca

That means the insertion of 10 will not be logged instead marked modified.

Then what if a crash occurs after that 10 insertion. Is it possible to restore from a log backup taken after that 10 insertions?

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

My 2 cents...

Depends on how you are inserting your records. If you are using normal insert statements then i believe it would behave just like the Full recovery model.

From BOL

Bulk-logged recovery model
This model minimally logs most bulk operations, such as index creation and bulk loads, while fully logging other transactions.

"Keep Trying"

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

This works for minimally logged operations, such as BULK INSERT.

For "normal" inserts the operation is fully logged.

BULK LOGGED recovery model does not allow point in time recovery.

BOL recommends backing up transaction log immediately before and after switching to/from BULK LOGGED to reactivate the point in time recovery.

-- Gianluca Sartori

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 5

VastSQL

SSC-Dedicated

Points: 36097

August 11, 2009 at 8:55 am

#1037486

Thanks All

Victor Alvarez-1138365 SSC-Addicted Points: 494 More actions · Answer 6

Hello Ratheesh.K.Nair,

Is important to be aware that when SQL Server is in Bulk logged RM logged in minimum way the Bulk operations, it means, SELECT INTO, BCP, BULK INTO. If you insert a million of records with a normal insert and if you do not have any bulk operations you can recover your db to any point in time. BUT, if you just one Bulk operation between the 2 backup log you can recover just to end of logs of the second log backup.

For details please check:

http://sqlpost.blogspot.com/2009/05/normal-0-false-false-false.html

With Regards,

Victor Alvarez
http://sqlpost.blogspot.com

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 7

Thanks Victor,

That is point in time recovery is not possible when we have bulk insert operation. Hope i am right 🙂

Victor Alvarez-1138365 SSC-Addicted Points: 494 More actions · Answer 8

Yes you are right.

Point in time recovery is not possible when the DB is in Bulk Logged RM and also if a bulk operation existed. In this case just is possible to recover to the immediate backup after bulk operations.

Victor Alvarez
http://sqlpost.blogspot.com

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

Just because a DB is in the bulk logged recovery mode, doesn't mean that the simple use of either BCP or BULK INSERT will meet the requirements of a bulk logged operation. There are other requirements to be met any one of which will prevent a bulk logged operation. You can look them up in BOL.

What I'm saying is that bulk operations CAN be logged and be available for point in time recovery.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 10

Hello Jeff/Victor and Rateesh,

I am confused with following statement.

"For log backup that contains bulk operations, the database can be recovered just to end of the log backup and the point in time (STOPAT) recovery is not allowed. If the log backup does not have bulk log operations logged, then the point in time recovery is allowed."

Take a scenario as follows:

1. TRUNCATE Table BULKInsertDemo.

2. ALTER DATABASE database_name SET RECOVERY BULK_LOGGED

3. BULK INSERT is performed for BULKInsertDemo.

4. ALTER DATABASE database_name SET RECOVERY FULL

5. Take the log backup.

(Note: This log backup doesn't contain any log). Now DB crashes. I am sure that we shall lose the data which is inserted during BULK INSERT.

-LK

spaghettidba SSC Guru Points: 105732 More actions · Answer 11

When you turn back to FULL recovery and take a log backup, you can restore to any point in time subsequent to reverting to FULL recovery.

But this is not the main point: the only thing you should remember is that a database in BULK_LOGGED recovery can be restored to a point in time only if no minimally logged operation was performed.

BOL states:

Restrictions for point-in-time recovery
If a log backup taken under the bulk-logged recovery model contains bulk-logged changes, point-in-time recovery is not allowed. Trying to perform point-in-time recovery on a log backup that contains bulk changes will cause the restore operation to fail.

and again:

If a log backup contains any bulk-logged operations, you cannot restore to a point-in-time within that log backup; you can restore only the whole log backup.

This means that it's not important what recovery model is applied to the database NOW, but what recovery model was selected when the log backup was taken.

Hope this is clear enough

Regards

Gianluca

-- Gianluca Sartori

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 12

Gianlucca,

Are you really sure and do you have practical experience of this as well? The reason I am asking is how the database log would contain the data which is inserted. The log would not have any information other than the changed page ids as we changed this to Bulk Logged mode while performing BULK INSERT. According to me we have to take full backup after bulk insert is done to make sure that data loss can be avoided.

-LK

spaghettidba SSC Guru Points: 105732 More actions · Answer 13

The log backup contains both log entries and data pages modified by bulk operations.

Always from BOL:

Under the bulk-logged recovery model, if a log backup covers any bulk operations, the log backup contains both log records and the data pages that were changed by bulk operations. This is necessary to capture the results of the bulk-logged operations. The incorporated data extents can make a log backup very large. Additionally, backing up the log requires access to the data files that contain the bulk-logged transactions. If any affected database file is inaccessible, the transaction log cannot be backed up and all operations committed in that log are lost.
To track the data pages, a log backup operation relies on a bulk-changes bitmap page that contains a bit for every extent. For each extent updated by a bulk-logged operation since the last log backup, the bit is set to 1 in the bitmap. The data extents are copied into the log followed by the log data.

-- Gianluca Sartori

Yanum_Vlad Grasshopper Points: 19 More actions · Answer 14

Dear all,

I still do not catch, how Full (!) log backup works when Bulk Minimal logging is in place.

BOL states:

Under the bulk-logged recovery model, if a log backup covers any bulk operations, the log backup contains both log records and the data pages that were changed by bulk operations.

But what about Full recovery model?

https://msdn.microsoft.com/en-us/library/ms190203.aspx

What does mean #4 there?

As for me, I would really make full / deferential backup to ensure no data loss.

Thanks,

Vlad