March 8, 2011 at 7:02 am
HI,
1. What exactly informations get logged in log file if my database is in Simple Recovery Model in SQL Server 2005 ?
2. What exactly minimal informations get logged if my database is in Bulk Log Recovery Model ?
Thanks in advance.
March 8, 2011 at 7:23 am
May I suggest you read up on logging in Books Online?
Short answer: Every data change is logged. In both simple and bulk-logged recovery models, operations that qualify for minimal logging (see Books Online, minimal logging) is minimally logged, ie just the page allocations are logged, not the details of the rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2011 at 7:35 am
Everything gets logged in the tlog in all cases. This is required for the database to be consistent (ACID). Please see today's editorial.
The difference is that in simple mode, the tlog 'history' is not retained. Once the transaction is committed and the changes fully saved in the .mdf (after the next checkpoint - whenever that happens next), then the already-saved transactions which were logged, are marked as 'available to be overwritten'. I'm talking about the tlog (.ldf) and not the real data table. The existing currently active transactions (in the process of being logged at the time of a checkpoint, but are still processing) are left alone and not marked as available (not 'truncated'). They will be truncated at the. Next checkpoint if they are fully done processing (committed) by then.
Bulk log is in between full (retaining the full history of the transactions in the log file until a backup of the log), and simple (only retaining history until the next checkpoint). Bulk log does not retain tlog history for certain bulk data modifications, like a reindex (and other cases). It does keep the full history for all non-bulk activity.
Search around in the forums and read articles to get more info.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 8, 2011 at 7:38 am
[Jim].[dba].[Murphy] (3/8/2011)
Bulk log does not retain tlog history for certain bulk data modifications, like a reindex (and other cases). It does keep the full history for all non-bulk activity.
Er, no, that's not how it works.
Bulk logged, like full recovery, keeps anything that is written to the log until it is backed up. It does not discard any log history. The difference from full is that, for some operations (see operations that can be minimally logged in Books Online) only the page allocations are logged and not the actual row inserts (as would happen in full recovery)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2011 at 7:42 am
Thanks guys for your all valuable replies. 🙂
March 8, 2011 at 7:51 am
Right. Thanks Gail. <pounding head on keyboard>
I appreciate the clarity and had a funny feeling that I was missing something when I clicked post. Next time, I'll double check first.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 11, 2011 at 4:49 am
guys, if everything gets logged in log file in Simple and Bulk Logged recovery then why we are not able to restore point in time recovery.
March 11, 2011 at 4:50 am
can you please elaborate it for my better understanding.
March 11, 2011 at 4:55 am
beejug1983 (3/11/2011)
guys, if everything gets logged in log file in Simple and Bulk Logged recovery then why we are not able to restore point in time recovery.
In simple because the log is truncated (marked as reusable) every time a checkpoint occurs allowing log records to be overwritten.
In bulk logged you can restore to point in time unless there was a minimally logged operation during the period covered by that log backup. If there is, you can only restore to the time the log backup was taken, not any random point in that interval
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2011 at 6:53 am
GilaMonster (3/11/2011)
In bulk logged you can restore to point in time unless there was a minimally logged operation during the period covered by that log backup. If there is, you can only restore to the time the log backup was taken, not any random point in that interval
Edited per Gail's comments below:
...And to regain point in time recovery once again, you could perform another log backup. Point in time recovery will be available again, after the next log backup following a minimally logged activity.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 11, 2011 at 8:59 am
[Jim].[dba].[Murphy] (3/11/2011)
GilaMonster (3/11/2011)
In bulk logged you can restore to point in time unless there was a minimally logged operation during the period covered by that log backup. If there is, you can only restore to the time the log backup was taken, not any random point in that interval...And to regain point in time recovery once again, you would need to perform a full backup to start a new tlog backup chain.
No you would not.
A bulk operation doesn't break the log chain. It only prevents point in time restores to a point within the log backup that includes the bulk operation.
So, let's say we have a DB in bulk-logged recovery model with the following sequence of events.
10:00 - Log backup
10:30 - Log backup
10:40 - Minimally logged operation (bcp, bulk insert, etc)
11:00 - Log backup
11:30 - Log backup
You can restore that DB to any time between 10:00 and 10:30, any time between 11:00 and 11:30 but, because of the minimally logged operation you cannot restore to any point between 10:30 and 11:00, only to the times of the log backup (10:30 or 11:00)
If you want me to explain in detail why that is the case, I can.
The only things that break the log chain are explicit log truncations (backup log ... truncate only) or a switch to simple recovery and back.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2011 at 9:36 am
Gail - Thanks! In older versions of SQL Server, when a minimally logged action occurred (bcp, etc.) in bulk-logged recovery mode, TLog backups thereafter FAILED with an error noting that a minimally logged action occurred. http://support.microsoft.com/kb/272093 Crazy right? Because of this, 12 years ago, I decided that Bulk-Logging was not for a production database and haven't used it since.
Pardon my old timer knowledge. Although I manage 2000, 2005 and 2008 (and one Azure) clients, I have all production DB's in full because of old history. I'm refreshing my skills/certification for 2008 so I don't spread misinformation based on times past.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply