Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Recovery Models Expand / Collapse
Author
Message
Posted Thursday, September 1, 2011 11:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
One undo of a truncate coming right up...

CREATE TABLE TestingTruncate (SomeUselessColumn varchar(50))

INSERT INTO TestingTruncate
SELECT NAME FROM sys.columns

SELECT * FROM TestingTruncate -- 501 rows

BEGIN TRANSACTION
TRUNCATE TABLE TestingTruncate
SELECT * FROM TestingTruncate -- 0 rows

ROLLBACK TRANSACTION

SELECT * FROM TestingTruncate -- 501 rows

btw, Create and Drop table are fully undoable as well.

BEGIN TRANSACTION 
DROP TABLE TestingTruncate
ROLLBACK TRANSACTION

SELECT * FROM TestingTruncate

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



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1168806
Posted Thursday, September 1, 2011 12:28 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:55 PM
Points: 149, Visits: 1,027
Yet another valuable lesson learned.

Regards,

Irish
Post #1168849
Posted Thursday, September 1, 2011 1:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 1:29 PM
Points: 17, Visits: 133
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.
Post #1168886
Posted Thursday, September 1, 2011 1:50 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:01 PM
Points: 1,787, Visits: 3,240
Thanks for the nice article I will bookmark it for my favourite.

One question about the below statement,
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.


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
Post #1168900
Posted Thursday, September 1, 2011 2:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1168913
Posted Thursday, September 1, 2011 9:13 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 1:04 PM
Points: 900, Visits: 1,490
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
Post #1169013
Posted Friday, September 2, 2011 4:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:12 AM
Points: 52, Visits: 92
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?

Post #1169131
Posted Friday, September 2, 2011 5:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1169164
Posted Friday, September 2, 2011 2:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 12:02 PM
Points: 42, Visits: 152
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.
Post #1169507
Posted Friday, September 2, 2011 2:06 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
Depends on what kind of disaster, what it damaged and what it left intact.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1169509
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse