September 1, 2011 at 9:13 pm
Excellent article and great discussion about it here! Thank you.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
September 2, 2011 at 4:44 am
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?
September 2, 2011 at 5:52 am
h.tobisch (9/2/2011)
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"
From Books Online:
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.
And a second question: what exactly cannot be recovered in case of minimal logging?
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)
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
September 2, 2011 at 2:02 pm
h.tobisch (9/2/2011)
Enjoyed the article.And a second question: what exactly cannot be recovered in case of minimal logging?
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?
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.
September 2, 2011 at 2:06 pm
Depends on what kind of disaster, what it damaged and what it left intact.
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
September 5, 2011 at 12:44 am
GilaMonster (9/2/2011)
Depends on what kind of disaster, what it damaged and what it left intact.
Would it make a difference if you were able to take a tail log backup of the database in the specified scenario?
September 6, 2011 at 1:05 am
Excellent article!
It's perfect for refreshing my memory on this very important subject.
September 6, 2011 at 2:36 am
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".
September 6, 2011 at 3:25 am
h.tobisch (9/6/2011)
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".
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.
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
September 6, 2011 at 7:41 am
Really nice article Gail, to understand the recovery models. Enjoyed it thoroughly 🙂
September 26, 2011 at 1:42 pm
Indeed a nice article, Gail
Another one to be ref-ed in the fora fairly frequent 😀
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply