SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Gail Shaw / An examination of bulk-logged recovery model / Latest Posts

RE: An examination of bulk-logged recovery model

kudz — Mon, 14 Jan 2013 15:55:20 GMT

I re-created the statement(with a new database) and everything went OK!But I really can't understand why I'm n0t getting minimal logging in the code posted on the following url:This is the steps I'am doing1º Create database2 º Set Recovery Model to Bulk Logged3º Verify on sys.databases the recovery model4º Backup database (.bak)5º SELECT INTO + SHUTDOWN WITH NOWAIT6º DELETE MDF7ºRESTART SQL8º BACKUP TAIL LOG WITH NO_TRUNCATE9ºRESTORE DATABASE WITH NORecovery10º RESTORE LOG WITH RECOVERY(Error)11 º RESTORE LGO WITH RECOVERY,CONTINUE_AFTER_ERROR(OK)12 º SELECT * From SomeTable( AAAAAAAA)Code Used: [url=http://pastebin.com/NZqkcKSY]http://pastebin.com/NZqkcKSY[/url]Thanks for your help[quote][b]GilaMonster (1/14/2013)[/b][hr]Just go over it a couple times, make sure you are getting minimal logging. I ran that particular test a number of times, as did a friend and, while we got a variety of different errors, we did not get the table back intact after any of the tests.[/quote]

RE: An examination of bulk-logged recovery model

GilaMonster — Mon, 14 Jan 2013 12:23:20 GMT

Just go over it a couple times, make sure you are getting minimal logging. I ran that particular test a number of times, as did a friend and, while we got a variety of different errors, we did not get the table back intact after any of the tests.

RE: An examination of bulk-logged recovery model

kudz — Mon, 14 Jan 2013 11:09:50 GMT

Thank you for your reply. As soon as i get to my office I will get all the code I have used and i will post here. [quote][b]GilaMonster (1/14/2013)[/b][hr]Without all your code, I can't say. Maybe the operations weren't minimally logged (existing table, full recovery or any of the other reasons for not minimally logging)[/quote]

RE: An examination of bulk-logged recovery model

GilaMonster — Mon, 14 Jan 2013 08:59:49 GMT

Without all your code, I can't say. Maybe the operations weren't minimally logged (existing table, full recovery or any of the other reasons for not minimally logging)

RE: An examination of bulk-logged recovery model

kudz — Mon, 14 Jan 2013 08:55:23 GMT

Thank you!I was redoing everything you have done in this example but when I restore the log with CONTINUE_AFTER_ERROR I get all my data correctly. I have done this multiple times and I haven't got the message 'Attempt to fetch logical page in database failed'I cannot understand why this is happening because when I did the log backup(tail) I already have deleted the mdf file , so there isn't any way SQL Server could get the data from.Can you please help me?Thank you

RE: An examination of bulk-logged recovery model

GilaMonster — Fri, 25 May 2012 05:11:24 GMT

[quote][b]crazy4sql (5/24/2012)[/b][hr]Should we live our life in bulk logged or full recovery mode?:hehe:[/quote]From the article:[quote]Guidelines for Bulk-logged RecoveryThe main guideline for bulk-logged recovery is: ‘as short a time as possible’.Due to the implications for point-in-time restores and restoring to point of failure, if a database is switched to bulk-logged recovery in order to minimally log some operations, it should stay in bulk-logged recovery for the shortest time possible. [/quote]

RE: An examination of bulk-logged recovery model

crazy4sql — Thu, 24 May 2012 12:05:08 GMT

Truly briefcase item for me. The complex topic in such a simple way( at least for me)Thanks GailShould we live our life in bulk logged or full recovery mode?:hehe:

RE: An examination of bulk-logged recovery model

sroos — Tue, 15 May 2012 02:32:24 GMT

[quote]If you've got a database where the data file is optimised for reading (say raid 5 and read caches) and a transaction log optimised for writing (say raid 10 and write caches), then it is possible that writing X GB of data to the data file will be slower than writing X GB of data to the log file would have been[/quote]Thanks Gail. This make sense.

RE: An examination of bulk-logged recovery model

GilaMonster — Mon, 14 May 2012 12:30:43 GMT

[quote][b]deepak_19888 (5/13/2012)[/b][hr]When we switch the database recovery mode, from Full recivery to bulk- logged or vice versa, does some changes happen to the existing log file of the database too??? (so as to align with the database recovery model).[/quote]The change is logged, just like any other change to the database, but there's nothing that you would need to do other than setting the recovery model option.

RE: An examination of bulk-logged recovery model

GilaMonster — Mon, 14 May 2012 11:00:43 GMT

[quote][b]stevro (5/13/2012)[/b][hr]I fail to fully understand why minimally logged operations can be slower . With the eager write process, should it not ease the burden on the transaction log by writing the dirty minimally logged records only to the data file? By splitting the writes as opposed to the fully logged model should it not be faster, especially if the log and data are on separate drives? Is flushing to the data files slower than writing to the log if both are sequential? I can understand it if the writes are random, but are minimal logged operations not all sequential in nature when writing to the data files?[/quote]With a fully logged operation only the log records have to be written to disk before the transaction is complete, the data pages can follow later. If you've got a database where the data file is optimised for reading (say raid 5 and read caches) and a transaction log optimised for writing (say raid 10 and write caches), then it is possible that writing X GB of data to the data file will be slower than writing X GB of data to the log file would have been, and the transaction commit will have to wait for the write to the data to the data file to finish where normally it only has to wait for the write to the log.I didn't say it will be slower, but it can be, maybe not usually, but it is a possibility.

RE: An examination of bulk-logged recovery model

mohammed moinudheen — Mon, 14 May 2012 04:58:03 GMT

Great reference article.

RE: An examination of bulk-logged recovery model

deepak_19888 — Sun, 13 May 2012 08:27:38 GMT

Hi Very nice Article.Since i am a beginner, i had a question in my mind:When we switch the database recovery mode, from Full recivery to bulk- logged or vice versa, does some changes happen to the existing log file of the database too??? (so as to align with the database recovery model).

RE: An examination of bulk-logged recovery model

sroos — Sun, 13 May 2012 05:30:33 GMT

Hi Gail,Thanks for the great article..One question:[quote]One side effect of this requirement that both log records and modified data pages are written to disk before the transaction commits is that it may actually result in the minimally logged operation being slower than a regular transaction if the data file is not able to handle the large volume of writes[/quote]I fail to fully understand why minimally logged operations can be slower . With the eager write process, should it not ease the burden on the transaction log by writing the dirty minimally logged records only to the data file? By splitting the writes as opposed to the fully logged model should it not be faster, especially if the log and data are on separate drives? Is flushing to the data files slower than writing to the log if both are sequential? I can understand it if the writes are random, but are minimal logged operations not all sequential in nature when writing to the data files?Thanks for this..

RE: An examination of bulk-logged recovery model

matt.bowler — Tue, 08 May 2012 20:00:19 GMT

Thanks Gail, great article and explanations. Clear and concise. Appreciate you clearing up that question for me.

RE: An examination of bulk-logged recovery model

DontPageMeBro — Tue, 08 May 2012 13:38:43 GMT

great article!

RE: An examination of bulk-logged recovery model

Ewald Cress — Tue, 08 May 2012 06:57:45 GMT

[quote]Please note that Eager Writes are just for minimally logged operations in bulk-logged or simple recovery, and they just write the minimally logged pages to the data file.[/quote]Understood. Mulling it over, this seems to make sense to me as a quick and dirty mental model for bulk logged operations:We always need "before" and "after" images in user database to support recovery. For minimally logged operations in bulk-logged or simple recovery, the "after" image is only in the data file, hence the absolute need for Eager Write. The "before" image is assumed to consist of empty pages (allocation updates aside), hence the restriction on what can and cannot be minimally logged. So rollforward is based on the knowledge that the data is in the data file, and rollback is only possible because we know we are rolling back to empty pages.

RE: An examination of bulk-logged recovery model

GilaMonster — Tue, 08 May 2012 06:07:48 GMT

[quote][b]Ewald Cress (5/8/2012)[/b][hr]I thought I had a decent grasp of logging issues, but I must admit that I had a light-bulb moment when you described the Eager Write mechanism![/quote]Please note that Eager Writes are just for minimally logged operations in bulk-logged or simple recovery, and they just write the minimally logged pages to the data file.

RE: An examination of bulk-logged recovery model

Ewald Cress — Tue, 08 May 2012 06:00:53 GMT

Thanks Gail, great article.I thought I had a decent grasp of logging issues, but I must admit that I had a light-bulb moment when you described the Eager Write mechanism!

RE: An examination of bulk-logged recovery model

GilaMonster — Tue, 08 May 2012 05:47:55 GMT

[quote][b]ChiragNS (5/8/2012)[/b][hr]Will this increase the log file size or only the log backup size?[/quote]Just the log backup. The pages don't go anywhere near the log file itself, they're just copied into the log backup when it is taken.

RE: An examination of bulk-logged recovery model

GilaMonster — Tue, 08 May 2012 05:47:13 GMT

[quote][b]matt.bowler (5/7/2012)[/b][hr]I do have one question: in the final demo where you looked at the log usage for an index rebuild, you mentioned "log reservation in case of rollback". Could you clarify what you mean by this?[/quote]Sure.High level summary: If an operation is rolled back, SQL performs the undo by reading the transaction log and generating compensating operations to reverse the effects of whatever is being rolled back. Those operations, like any other data modification, get logged.A rollback cannot be allowed to fail if say the rollback is caused by a full transaction log, that would result in the database being marked suspect, which is a very bad thing.Hence, while SQL is performing any data modification and logging the changes, it also reserves enough space in the log to be able to log the undo of those operations. This is log reservation. The estimation that SQL uses is a very conservative one, it's better to reserve more log space than is actually needed than to not reserve enough and send the DB suspect.[url]http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-25-Why-isnt-my-log-backup-the-same-size-as-my-log.aspx[/url]

RE: An examination of bulk-logged recovery model

ChiragNS — Tue, 08 May 2012 05:22:53 GMT

Great article Gail. One doubt though - you mentioned that if we do a log backup when a minimally logged operation is running the log backup contains the "image" of pages being modified by the minimally logged operation.Will this increase the log file size or only the log backup size?Thanks

RE: An examination of bulk-logged recovery model

iugdds — Mon, 07 May 2012 19:18:32 GMT

==== ( http://www.fullmalls.com ) ========= ( http://www.fullmalls.com ) =====$33 True Religion jeans, Ed Hardy jeans,LV,Coogi jeans,Affliction jeans $30 Air Jordan shoes,Shox shoes,Gucci,LV shoes50%Discount winter fashion :Sandle,t-shirt,caps,jerseys,handbag and brand watches!!!$15 Ed Hardy ,LV ,Gucci Bikini$15 Polo, Ed Hardy, Gucci, LV, Lacoste T-shirts$25 Coach,Gucci,LV,Prada,Juicy,Chanel handbag,$10 Gucci,Ed Hardy sunglasses$9 New Era caps.give you the unexpected harvest==== ( http://www.scnshop.com ) ========= ( http://www.scnshop.com ) ========= ( http://www.fullmalls.com ) ========= ( http://www.fullmalls.com ) ========= ( http://www.fullmalls.com ) ========= ( http://www.fullmalls.com ) =====

RE: An examination of bulk-logged recovery model

iugdds — Mon, 07 May 2012 19:18:29 GMT

RE: An examination of bulk-logged recovery model

iugdds — Mon, 07 May 2012 19:18:28 GMT

RE: An examination of bulk-logged recovery model

iugdds — Mon, 07 May 2012 19:18:23 GMT

RE: An examination of bulk-logged recovery model

matt.bowler — Mon, 07 May 2012 16:36:04 GMT

Thank you Gail for the excellent article.I do have one question: in the final demo where you looked at the log usage for an index rebuild, you mentioned "log reservation in case of rollback". Could you clarify what you mean by this?

RE: An examination of bulk-logged recovery model

Chintak Chhapia — Mon, 07 May 2012 14:02:19 GMT

Superb Article...

RE: An examination of bulk-logged recovery model

Carla Wilson-484785 — Mon, 07 May 2012 07:52:17 GMT

Awesome!Thank you, Gail, for taking the time to write such a thorough, detailed article.

RE: An examination of bulk-logged recovery model

Chris Houghton — Mon, 07 May 2012 06:43:50 GMT

Thank you Gail, your article has really helped further my understanding of this rather murky area. It's great that you and other experts take the time out to help the community, it really is appreciated.

RE: An examination of bulk-logged recovery model

Lian Pretorius — Mon, 07 May 2012 03:12:42 GMT

Thanks Gail.Well worth the read!

An examination of bulk-logged recovery model

GilaMonster — Sat, 05 May 2012 20:36:01 GMT

Comments posted to this topic are about the item [B]An examination of bulk-logged recovery model[/B]