Dear experts,
I am confused. I always thought that using NORECOVERY was essential when
restoring multiple transactional log backups in a row and I always thought
this was due to the fact that without NORECOVERY the transactional log
backups wouldn't "fit" to each other, because of potential rollbacks
happening in recovery. E.g. I have a tlb (transactional log backup, for
brevity's sake) ending at LSN 124. I restore with recovery and transaction
106 isn't commited before LSN 124, so it gets rolled back. Now I try to
restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
because transaction 106 is commited at LSN 145 after all! I thought this is
why you have to use NORECOVERY.
BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
Server 2005 Implementation and Maintenance" that made me think otherwise
(pg. 419): "A log backup backs up the active log. It starts at the Log
Sequence Number (LSN) at which the previous log backup completed. SQL Server
then backs up all subsequent transactions UNTIL THE BACKUP ENCOUNTERS AN
OPEN TRANSACTION." (emphasis mine)
Now if this is so, why is there any need for a rollback after restoring a
tlb with recovery anyway? All transactions included in the tlb are not open
(i.e. commited or rolled backed), so the worst thing that could happen is
the need for a rollforward, in case the db isn't consistent with the tlb. So
why do we need NORECOVERY?
Somewhat related bonus question: What portion of the log is exactly backed
up when I do a FULL backup? It's only the part after the oldest open
transaction, right?
Thank you a lot
Nils LoeberHi Nils
This is not correct, but I appreciate your recognition of the quality of the
document as a whole, even though there are some errors.
Books Online is correct in stating that during a log backup:
the log is backed up from the last successfully executed log backup to
the current end of the log.
I have no idea where the comment about open transaction came from.
When you do a full backup, SQL Server records the current LSN when the full
backup starts. When the full backup is over, the new current LSN is records.
All the log records between the two recorded LSNs are then backed up. So it
basically captures all other changes that were going on while the backup was
taking place. (It may start much later than the oldest open transaction.)
HTH
Kalen Delaney, SQL Server MVP
"Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
> Dear experts,
> I am confused. I always thought that using NORECOVERY was essential when
> restoring multiple transactional log backups in a row and I always thought
> this was due to the fact that without NORECOVERY the transactional log
> backups wouldn't "fit" to each other, because of potential rollbacks
> happening in recovery. E.g. I have a tlb (transactional log backup, for
> brevity's sake) ending at LSN 124. I restore with recovery and transaction
> 106 isn't commited before LSN 124, so it gets rolled back. Now I try to
> restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
> because transaction 106 is commited at LSN 145 after all! I thought this
> is why you have to use NORECOVERY.
> BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
> Server 2005 Implementation and Maintenance" that made me think otherwise
> (pg. 419): "A log backup backs up the active log. It starts at the Log
> Sequence Number (LSN) at which the previous log backup completed. SQL
> Server then backs up all subsequent transactions UNTIL THE BACKUP
> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
> Now if this is so, why is there any need for a rollback after restoring a
> tlb with recovery anyway? All transactions included in the tlb are not
> open (i.e. commited or rolled backed), so the worst thing that could
> happen is the need for a rollforward, in case the db isn't consistent with
> the tlb. So why do we need NORECOVERY?
> Somewhat related bonus question: What portion of the log is exactly backed
> up when I do a FULL backup? It's only the part after the oldest open
> transaction, right?
>
> Thank you a lot
> Nils Loeber
>|||Hi Kalen,
this is the kind of helpful answer I had hoped for. Thank you very much.
Best regards
Nils Loeber
"Kalen Delaney" <replies@.public_newsgroups.com> schrieb im Newsbeitrag
news:OYJ2dQmuGHA.2260@.TK2MSFTNGP03.phx.gbl...
> Hi Nils
> This is not correct, but I appreciate your recognition of the quality of
> the document as a whole, even though there are some errors.
> Books Online is correct in stating that during a log backup:
> the log is backed up from the last successfully executed log backup to
> the current end of the log.
> I have no idea where the comment about open transaction came from.
> When you do a full backup, SQL Server records the current LSN when the
> full backup starts. When the full backup is over, the new current LSN is
> records. All the log records between the two recorded LSNs are then backed
> up. So it basically captures all other changes that were going on while
> the backup was taking place. (It may start much later than the oldest open
> transaction.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
> news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
>
Showing posts with label dear. Show all posts
Showing posts with label dear. Show all posts
Friday, March 9, 2012
Question on Log Backup and NORECOVERY
Dear experts,
I am confused. I always thought that using NORECOVERY was essential when
restoring multiple transactional log backups in a row and I always thought
this was due to the fact that without NORECOVERY the transactional log
backups wouldn't "fit" to each other, because of potential rollbacks
happening in recovery. E.g. I have a tlb (transactional log backup, for
brevity's sake) ending at LSN 124. I restore with recovery and transaction
106 isn't commited before LSN 124, so it gets rolled back. Now I try to
restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
because transaction 106 is commited at LSN 145 after all! I thought this is
why you have to use NORECOVERY.
BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
Server 2005 Implementation and Maintenance" that made me think otherwise
(pg. 419): "A log backup backs up the active log. It starts at the Log
Sequence Number (LSN) at which the previous log backup completed. SQL Server
then backs up all subsequent transactions UNTIL THE BACKUP ENCOUNTERS AN
OPEN TRANSACTION." (emphasis mine)
Now if this is so, why is there any need for a rollback after restoring a
tlb with recovery anyway? All transactions included in the tlb are not open
(i.e. commited or rolled backed), so the worst thing that could happen is
the need for a rollforward, in case the db isn't consistent with the tlb. So
why do we need NORECOVERY?
Somewhat related bonus question: What portion of the log is exactly backed
up when I do a FULL backup? It's only the part after the oldest open
transaction, right?
Thank you a lot
Nils LoeberHi Nils
This is not correct, but I appreciate your recognition of the quality of the
document as a whole, even though there are some errors.
Books Online is correct in stating that during a log backup:
the log is backed up from the last successfully executed log backup to
the current end of the log.
I have no idea where the comment about open transaction came from.
When you do a full backup, SQL Server records the current LSN when the full
backup starts. When the full backup is over, the new current LSN is records.
All the log records between the two recorded LSNs are then backed up. So it
basically captures all other changes that were going on while the backup was
taking place. (It may start much later than the oldest open transaction.)
--
HTH
Kalen Delaney, SQL Server MVP
"Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
> Dear experts,
> I am confused. I always thought that using NORECOVERY was essential when
> restoring multiple transactional log backups in a row and I always thought
> this was due to the fact that without NORECOVERY the transactional log
> backups wouldn't "fit" to each other, because of potential rollbacks
> happening in recovery. E.g. I have a tlb (transactional log backup, for
> brevity's sake) ending at LSN 124. I restore with recovery and transaction
> 106 isn't commited before LSN 124, so it gets rolled back. Now I try to
> restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
> because transaction 106 is commited at LSN 145 after all! I thought this
> is why you have to use NORECOVERY.
> BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
> Server 2005 Implementation and Maintenance" that made me think otherwise
> (pg. 419): "A log backup backs up the active log. It starts at the Log
> Sequence Number (LSN) at which the previous log backup completed. SQL
> Server then backs up all subsequent transactions UNTIL THE BACKUP
> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
> Now if this is so, why is there any need for a rollback after restoring a
> tlb with recovery anyway? All transactions included in the tlb are not
> open (i.e. commited or rolled backed), so the worst thing that could
> happen is the need for a rollforward, in case the db isn't consistent with
> the tlb. So why do we need NORECOVERY?
> Somewhat related bonus question: What portion of the log is exactly backed
> up when I do a FULL backup? It's only the part after the oldest open
> transaction, right?
>
> Thank you a lot
> Nils Loeber
>|||Hi Kalen,
this is the kind of helpful answer I had hoped for. Thank you very much.
Best regards
Nils Loeber
"Kalen Delaney" <replies@.public_newsgroups.com> schrieb im Newsbeitrag
news:OYJ2dQmuGHA.2260@.TK2MSFTNGP03.phx.gbl...
> Hi Nils
> This is not correct, but I appreciate your recognition of the quality of
> the document as a whole, even though there are some errors.
> Books Online is correct in stating that during a log backup:
> the log is backed up from the last successfully executed log backup to
> the current end of the log.
> I have no idea where the comment about open transaction came from.
> When you do a full backup, SQL Server records the current LSN when the
> full backup starts. When the full backup is over, the new current LSN is
> records. All the log records between the two recorded LSNs are then backed
> up. So it basically captures all other changes that were going on while
> the backup was taking place. (It may start much later than the oldest open
> transaction.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
> news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
>> Dear experts,
>> I am confused. I always thought that using NORECOVERY was essential when
>> restoring multiple transactional log backups in a row and I always
>> thought this was due to the fact that without NORECOVERY the
>> transactional log backups wouldn't "fit" to each other, because of
>> potential rollbacks happening in recovery. E.g. I have a tlb
>> (transactional log backup, for brevity's sake) ending at LSN 124. I
>> restore with recovery and transaction 106 isn't commited before LSN 124,
>> so it gets rolled back. Now I try to restore the tlb beginning at LSN 125
>> and lo and behold, it doesn't work because transaction 106 is commited at
>> LSN 145 after all! I thought this is why you have to use NORECOVERY.
>> BUT: There is a sentence in Solid Quality Learning's fine publication
>> "SQL Server 2005 Implementation and Maintenance" that made me think
>> otherwise (pg. 419): "A log backup backs up the active log. It starts at
>> the Log Sequence Number (LSN) at which the previous log backup completed.
>> SQL Server then backs up all subsequent transactions UNTIL THE BACKUP
>> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
>> Now if this is so, why is there any need for a rollback after restoring a
>> tlb with recovery anyway? All transactions included in the tlb are not
>> open (i.e. commited or rolled backed), so the worst thing that could
>> happen is the need for a rollforward, in case the db isn't consistent
>> with the tlb. So why do we need NORECOVERY?
>> Somewhat related bonus question: What portion of the log is exactly
>> backed up when I do a FULL backup? It's only the part after the oldest
>> open transaction, right?
>>
>> Thank you a lot
>> Nils Loeber
>
I am confused. I always thought that using NORECOVERY was essential when
restoring multiple transactional log backups in a row and I always thought
this was due to the fact that without NORECOVERY the transactional log
backups wouldn't "fit" to each other, because of potential rollbacks
happening in recovery. E.g. I have a tlb (transactional log backup, for
brevity's sake) ending at LSN 124. I restore with recovery and transaction
106 isn't commited before LSN 124, so it gets rolled back. Now I try to
restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
because transaction 106 is commited at LSN 145 after all! I thought this is
why you have to use NORECOVERY.
BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
Server 2005 Implementation and Maintenance" that made me think otherwise
(pg. 419): "A log backup backs up the active log. It starts at the Log
Sequence Number (LSN) at which the previous log backup completed. SQL Server
then backs up all subsequent transactions UNTIL THE BACKUP ENCOUNTERS AN
OPEN TRANSACTION." (emphasis mine)
Now if this is so, why is there any need for a rollback after restoring a
tlb with recovery anyway? All transactions included in the tlb are not open
(i.e. commited or rolled backed), so the worst thing that could happen is
the need for a rollforward, in case the db isn't consistent with the tlb. So
why do we need NORECOVERY?
Somewhat related bonus question: What portion of the log is exactly backed
up when I do a FULL backup? It's only the part after the oldest open
transaction, right?
Thank you a lot
Nils LoeberHi Nils
This is not correct, but I appreciate your recognition of the quality of the
document as a whole, even though there are some errors.
Books Online is correct in stating that during a log backup:
the log is backed up from the last successfully executed log backup to
the current end of the log.
I have no idea where the comment about open transaction came from.
When you do a full backup, SQL Server records the current LSN when the full
backup starts. When the full backup is over, the new current LSN is records.
All the log records between the two recorded LSNs are then backed up. So it
basically captures all other changes that were going on while the backup was
taking place. (It may start much later than the oldest open transaction.)
--
HTH
Kalen Delaney, SQL Server MVP
"Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
> Dear experts,
> I am confused. I always thought that using NORECOVERY was essential when
> restoring multiple transactional log backups in a row and I always thought
> this was due to the fact that without NORECOVERY the transactional log
> backups wouldn't "fit" to each other, because of potential rollbacks
> happening in recovery. E.g. I have a tlb (transactional log backup, for
> brevity's sake) ending at LSN 124. I restore with recovery and transaction
> 106 isn't commited before LSN 124, so it gets rolled back. Now I try to
> restore the tlb beginning at LSN 125 and lo and behold, it doesn't work
> because transaction 106 is commited at LSN 145 after all! I thought this
> is why you have to use NORECOVERY.
> BUT: There is a sentence in Solid Quality Learning's fine publication "SQL
> Server 2005 Implementation and Maintenance" that made me think otherwise
> (pg. 419): "A log backup backs up the active log. It starts at the Log
> Sequence Number (LSN) at which the previous log backup completed. SQL
> Server then backs up all subsequent transactions UNTIL THE BACKUP
> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
> Now if this is so, why is there any need for a rollback after restoring a
> tlb with recovery anyway? All transactions included in the tlb are not
> open (i.e. commited or rolled backed), so the worst thing that could
> happen is the need for a rollforward, in case the db isn't consistent with
> the tlb. So why do we need NORECOVERY?
> Somewhat related bonus question: What portion of the log is exactly backed
> up when I do a FULL backup? It's only the part after the oldest open
> transaction, right?
>
> Thank you a lot
> Nils Loeber
>|||Hi Kalen,
this is the kind of helpful answer I had hoped for. Thank you very much.
Best regards
Nils Loeber
"Kalen Delaney" <replies@.public_newsgroups.com> schrieb im Newsbeitrag
news:OYJ2dQmuGHA.2260@.TK2MSFTNGP03.phx.gbl...
> Hi Nils
> This is not correct, but I appreciate your recognition of the quality of
> the document as a whole, even though there are some errors.
> Books Online is correct in stating that during a log backup:
> the log is backed up from the last successfully executed log backup to
> the current end of the log.
> I have no idea where the comment about open transaction came from.
> When you do a full backup, SQL Server records the current LSN when the
> full backup starts. When the full backup is over, the new current LSN is
> records. All the log records between the two recorded LSNs are then backed
> up. So it basically captures all other changes that were going on while
> the backup was taking place. (It may start much later than the oldest open
> transaction.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Nils Loeber" <nils@.NOSPAMFORMEPLEASEnils-loeber.de> wrote in message
> news:eIYWsFmuGHA.736@.TK2MSFTNGP02.phx.gbl...
>> Dear experts,
>> I am confused. I always thought that using NORECOVERY was essential when
>> restoring multiple transactional log backups in a row and I always
>> thought this was due to the fact that without NORECOVERY the
>> transactional log backups wouldn't "fit" to each other, because of
>> potential rollbacks happening in recovery. E.g. I have a tlb
>> (transactional log backup, for brevity's sake) ending at LSN 124. I
>> restore with recovery and transaction 106 isn't commited before LSN 124,
>> so it gets rolled back. Now I try to restore the tlb beginning at LSN 125
>> and lo and behold, it doesn't work because transaction 106 is commited at
>> LSN 145 after all! I thought this is why you have to use NORECOVERY.
>> BUT: There is a sentence in Solid Quality Learning's fine publication
>> "SQL Server 2005 Implementation and Maintenance" that made me think
>> otherwise (pg. 419): "A log backup backs up the active log. It starts at
>> the Log Sequence Number (LSN) at which the previous log backup completed.
>> SQL Server then backs up all subsequent transactions UNTIL THE BACKUP
>> ENCOUNTERS AN OPEN TRANSACTION." (emphasis mine)
>> Now if this is so, why is there any need for a rollback after restoring a
>> tlb with recovery anyway? All transactions included in the tlb are not
>> open (i.e. commited or rolled backed), so the worst thing that could
>> happen is the need for a rollforward, in case the db isn't consistent
>> with the tlb. So why do we need NORECOVERY?
>> Somewhat related bonus question: What portion of the log is exactly
>> backed up when I do a FULL backup? It's only the part after the oldest
>> open transaction, right?
>>
>> Thank you a lot
>> Nils Loeber
>
Monday, February 20, 2012
Question on attribute keys of measure group
Hi, dear friends,
I encountered a problem processing my cube with the error message telling: processing measure_group_name (which only has a count as its only measure)failed as attribute keys can not be found? Why is that? I have no idea why is that? Does it mean I have to include all the keys of the fact table where the measure group from in the measure groups as measures? Otherwise the system failed to identify the distinct count of the system? Or whatever measures the measure group contains, it alwys have to include all the keys including the dimension keys of dimensions which the measure group is related to?
Thanks in advance for your help and advices and I am always looking forward to hearing from you shortly from you.
With best regards,
Yours sincerely,
This message means that a column used to join the measure group to a dimension contains a value not found in the dimension. This is usually caused by a referential integrity error - the FK does not existing in the PK of the lookup table. However, it can also be caused by the presence of a null in the FK as by default this null will be converted to zero or blank which probably does not exist as a key in the dimension. You can handle the situation of nulls in FK columns within the fact table by setting the NullProcessing option in the advanced tab of the measure group relationship dialog from the Dimension Usage tab of the cube editor.|||Hi, Matt,
Thanks a lot. Yes, the problem is the matching of the PK and the FK between the tables.
However, the data source view logical key and the relationships between tables are really annoying then, as it is not able to check the integrity of the tables?
With best regards,
Yours sincerely,
|||So it sounds like you would like to have an option on the Data Source View to validate logical keys and relationships. I'd recommend you file a feature request on http://connect.microsoft.com/SQLServer/Feedback. Such feature requests will be automatically added to our issue tracking system for consideration in future releases and those that originate from a customer such as yourself get extra consideration since we know it's a real world scenario. If you can describe the scenario it will also help as it will allow us to better identify which requests will help the most people and how to implement them so that they actually provide the help intended.
Thanks, Matt
|||Hi, Matt,
Thanks a lot and this sounds really a good idea of it.
With best regards,
Yours sincerely,
Subscribe to:
Posts (Atom)