SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Gail Shaw / Recovery Models / Latest Posts

RE: Recovery Models

ALZDBA — Mon, 26 Sep 2011 13:42:45 GMT

Indeed a nice article, GailAnother one to be ref-ed in the fora fairly frequent :-D

RE: Recovery Models

Divine Flame — Tue, 06 Sep 2011 07:41:11 GMT

Really nice article Gail, to understand the recovery models. Enjoyed it thoroughly :-)

RE: Recovery Models

GilaMonster — Tue, 06 Sep 2011 03:25:04 GMT

[quote][b]h.tobisch (9/6/2011)[/b][hr]I am afraid I was not able to convey the problem I see behind my question: if . as stated by Gail, minimal logging records allcation changes by a minimally logged statement, it would not be possible to reconstruct data this statement puts into already existing allocation units. So I suppose it should read "allocation units affected by the statement".[/quote]But if you read the piece I quoted from BoL, Minimal logging requires that the table is empty or a heap. So only new extents (not allocation units) are affected by a minimally logged insert.

RE: Recovery Models

h.tobisch — Tue, 06 Sep 2011 02:36:32 GMT

I am afraid I was not able to convey the problem I see behind my question: if . as stated by Gail, minimal logging records allcation changes by a minimally logged statement, it would not be possible to reconstruct data this statement puts into already existing allocation units. So I suppose it should read "allocation units affected by the statement".

RE: Recovery Models

Håvard — Tue, 06 Sep 2011 01:05:18 GMT

Excellent article!It's perfect for refreshing my memory on this very important subject.

RE: Recovery Models

paul.knibbs — Mon, 05 Sep 2011 00:44:49 GMT

[quote][b]GilaMonster (9/2/2011)[/b][hr]Depends on what kind of disaster, what it damaged and what it left intact.[/quote]Would it make a difference if you were able to take a tail log backup of the database in the specified scenario?

RE: Recovery Models

GilaMonster — Fri, 02 Sep 2011 14:06:37 GMT

Depends on what kind of disaster, what it damaged and what it left intact.

RE: Recovery Models

wbrianwhite — Fri, 02 Sep 2011 14:02:46 GMT

[quote][b]h.tobisch (9/2/2011)[/b][hr]Enjoyed the article.And a second question: what exactly cannot be recovered in case of minimal logging?[/quote]AFAIK, the data that was minimally logged during the current log interval. Like with the example of the 50 GB bulk import. If you switch to bulk log, did the import, switched back to full and then there was a disaster immediately after and you hadn't backed up your log again, that minimally logged data in that log interval may be lost. Is that correct Gail?[quote]If a disaster occurs to a database in bulk-logged recovery, even if the log file is intact and undamaged, it may not be possible to take a tail-log backup. To take a tail-log backup of a database in bulk-logged recovery where the data file is missing or damaged, there must have been no bulk-operations since the last log backup. Similarly, to restore a database in bulk-logged recovery to a point in time, that time must be within a log interval (time covered by a single log backup) in which no minimally logged operations occurred. If any minimally logged operations occurred within a log interval, the database can be restored only to the beginning or end of that log interval, not to a point in the middle.Because of these limitations, having a database in bulk-logged recovery increase the chances of data loss in the case of a disaster. Hence it is more common to switch databases temporarily to bulk-logged recovery for certain operations (like index rebuilds) and then back to full recovery afterwards, than to have them in bulk-logged recovery permanently.....That said it's a very good idea to take a log backup after switching back to full recovery so that the current log interval does not contain any minimally-logged operations that could prevent tail-log backups or point-in-time recovery.[/quote]

RE: Recovery Models

GilaMonster — Fri, 02 Sep 2011 05:52:09 GMT

[quote][b]h.tobisch (9/2/2011)[/b][hr]You write: "An operation is said to be minimally-logged if all it logs are the allocation changes."In the case of a bulk insert this would mean either that a bulk insert can use only fresh allocation units (pages?) or that your statement must be rewritten to "logs which allocation units have been affected"[/quote]From Books Online:[quote]Whether minimal logging can occur for a table also depends on whether the table is indexed and, if so, whether the table is empty:If the table has no indexes, data pages are minimally logged.If the table has no clustered index but has one or more nonclustered indexes, data pages are always minimally logged. How index pages are logged, however, depends on whether the table is empty:* If the table is empty, index pages are minimally logged.* If table is non-empty, index pages are fully logged. If the table has a clustered index and is empty, both data and index pages are minimally logged. In contrast, if a table has a clustered index and is non-empty, data pages and index pages are both fully logged regardless of the recovery model.[/quote][quote]And a second question: what exactly cannot be recovered in case of minimal logging?[/quote]Don't understand the question. SQL ensures, no matter what, a database can be recovered in case of a shutdown (if it can't it goes suspect)

RE: Recovery Models

h.tobisch — Fri, 02 Sep 2011 04:44:12 GMT

Enjoyed the article.Thinking over it I came to this question:You write: "An operation is said to be minimally-logged if all it logs are the allocation changes."In the case of a bulk insert this would mean either that a bulk insert can use only fresh allocation units (pages?) or that your statement must be rewritten to "logs which allocation units have been affected"And a second question: what exactly cannot be recovered in case of minimal logging?

RE: Recovery Models

codebyo — Thu, 01 Sep 2011 21:13:07 GMT

Excellent article and great discussion about it here! Thank you.Best regards,

RE: Recovery Models

GilaMonster — Thu, 01 Sep 2011 14:10:22 GMT

In the context of the quote it's usually pretty clear. If there's been some disaster you will know (or easily be able to find out) whether the drive that the log is on is intact or not.Other than that, does a log backup succeed? If yes, the log file's intact and undamaged.

RE: Recovery Models

sqlfriends — Thu, 01 Sep 2011 13:50:10 GMT

Thanks for the nice article I will bookmark it for my favourite.One question about the below statement, [quote]Because of this behaviour, when a database is in full recovery model and the database is damaged in such a way that the transaction log is intact and undamaged, a tail-log backup can be taken which holds the last transactions that occurred in that database. Hence, assuming the database's transaction log is intact, it is possible to restore without data loss in case of a disaster.[/quote]How do we know if it is the data file or transaction log file is damaged?How can we check if a transaction log is intact or not?Thanks

RE: Recovery Models

gil.milbauer — Thu, 01 Sep 2011 13:26:39 GMT

Good article.I know people like lists of myths, but I remember reading that the way our memories work is often context-free, and sometimes these lists reinforce the myths in people's minds if they don't spend much time trying to integrate the detailed debunking into what they know.I'm not sure what to do with that, other than maybe to try to present the negation of the myth in emphasized form, rather than the myths themselves.

RE: Recovery Models

Jeffrey Irish — Thu, 01 Sep 2011 12:28:19 GMT

Yet another valuable lesson learned.

RE: Recovery Models

GilaMonster — Thu, 01 Sep 2011 11:11:46 GMT

One undo of a truncate coming right up...[code="sql"]CREATE TABLE TestingTruncate (SomeUselessColumn varchar(50))INSERT INTO TestingTruncate SELECT NAME FROM sys.columnsSELECT * FROM TestingTruncate -- 501 rowsBEGIN TRANSACTIONTRUNCATE TABLE TestingTruncateSELECT * FROM TestingTruncate -- 0 rowsROLLBACK TRANSACTIONSELECT * FROM TestingTruncate -- 501 rows[/code]btw, Create and Drop table are fully undoable as well.[code="sql"]BEGIN TRANSACTION DROP TABLE TestingTruncateROLLBACK TRANSACTIONSELECT * FROM TestingTruncate[/code]A similar example with create table is left as an exercise to the reader.If an operation didn't write log entries for undo, then if that operation was part of a transaction and the transaction failed, the entire database would have to be marked suspect.If an operation didn't write log entries for redo then neither mirroring nor log shipping (nor transactional replication) would be able to repeat that operation at the mirror/secondary

RE: Recovery Models

Jeffrey Irish — Thu, 01 Sep 2011 10:54:39 GMT

Gail,I always enjoy reading your posts and blogs. It is awesome that you are so willing to share your knowledge and experience.One of the items you noted is [quote]Truncate table, for example, only logs the page de-allocations (as does drop table), but since that is enough information to fully redo the truncate table, that is classified as ‘fully logged’[/quote]Now I know that Truncate Table, like Create Table or Alter Table, are Data Definition Language (DDL) and I always thought that they were stand-alone operations and could not be redone or undone. Now I know at the very least they are logged for "Redo" operations, but am I correct in thinking that there is still no "undo" short of completing a point-in-time recovery before the DDL operation was run?

RE: Recovery Models

Jack Corbett — Thu, 01 Sep 2011 07:13:04 GMT

Good article with simple concise explanations. I actually don't remember knowing that the Simple recovery model had minimally logged operations. I thought the logging was the same as FULL. Learned something new even though I thought I understood the topic.One note, I usually change the model database to simple because I have the model log grow and I don't want or need to do log backups. This does mean I need to specifically set the recovery model to FULL for new databases that I know need log backups, but I normally do that anyway.

RE: Recovery Models

GilaMonster — Thu, 01 Sep 2011 04:08:23 GMT

[quote][b]paul.knibbs (9/1/2011)[/b][hr]I'm still not sure this offers a major advantage! You're just moving the 50Gb of disk space usage from one place to another.[/quote]The log backup if you'd done the load in full recovery would also be around 50GB or more (the entire used log space would get backed up). It's also a time advantage not just a space one. Less logging = less overhead = faster load.

RE: Recovery Models

paul.knibbs — Thu, 01 Sep 2011 04:02:04 GMT

True, but since you need to switch it back to full recovery and then take a log backup--which, as the article said, will easily be the full 50Gb and change thanks to having to include all the altered pages--I'm still not sure this offers a major advantage! You're just moving the 50Gb of disk space usage from one place to another.

RE: Recovery Models

GilaMonster — Thu, 01 Sep 2011 02:58:36 GMT

[quote][b]paul.knibbs (9/1/2011)[/b][hr]Nice article, but it just reinforces a query I've had for a while: why does bulk-logged recovery mode even exist? It has some hefty downsides compared to full recovery, and the only advantage I can see is that your log file might not get quite so big when using it, which seems a fairly minimal sort of advantage to my mind. Does someone with more SQL experience than me (i.e. practically everybody) have an example of a situation where it's really better to use bulk-logged recovery mode?[/quote]When you want to do major data loads, you don't want the overhead of full logging (nor the log growth) but you don't want to switch to simple recovery.Let's say someone's bulk-inserting 50GB of data (maybe a bit extreme). In full recovery, that's at least 50GB of log space used. In bulk-logged, it's a lot less.

RE: Recovery Models

GilaMonster — Thu, 01 Sep 2011 02:56:50 GMT

[quote][b]David.Poole (9/1/2011)[/b][hr]I believe that it isn't simply a case of truncating on checkpoint for simple mode. I could be wrong but I thought it was a case of the truncation occuring for checkpointed transactions when the transaction log reaches a certain percentage of fullness i.e. 70%[/quote]Checkpoint truncates the log every time it runs. You can prove that by running various data-modification queries, running checkpoint and then checking fn_dblog (I do that any time I want to examine what an operation logs)That 70% (iirc) is specifically for TempDB. It has some very different rules

RE: Recovery Models

David.Poole — Thu, 01 Sep 2011 01:55:10 GMT

Gail,I believe that it isn't simply a case of truncating on checkpoint for simple mode. I could be wrong but I thought it was a case of the truncation occuring for checkpointed transactions when the transaction log reaches a certain percentage of fullness i.e. 70%

RE: Recovery Models

mohammed moinudheen — Thu, 01 Sep 2011 01:20:06 GMT

Excellent article Gail. I liked all the myths and misconceptions you mentioned escpecially this one.[i]Myth 4: You have to switch from simple recovery to bulk-logged recovery model to get minimally logged operations.[/i]

RE: Recovery Models

paul.knibbs — Thu, 01 Sep 2011 00:53:47 GMT

Nice article, but it just reinforces a query I've had for a while: why does bulk-logged recovery mode even exist? It has some hefty downsides compared to full recovery, and the only advantage I can see is that your log file might not get quite so big when using it, which seems a fairly minimal sort of advantage to my mind. Does someone with more SQL experience than me (i.e. practically everybody) have an example of a situation where it's really better to use bulk-logged recovery mode?

Recovery Models

GilaMonster — Thu, 01 Sep 2011 00:05:44 GMT

Comments posted to this topic are about the item [B]Recovery Models[/B]