Solved

doubt in recovery process in mssql

Posted on 2011-09-09
22
272 Views
Last Modified: 2012-05-12
I take a full backup of database say@ 8.00 am

and have inserted a 100 records @8.30 am
and have updated a data @ 8.35 am
now i have deleted around 10000 rows record @ 8.45 am

now I want to recover data till 8.44 am
First i will perform backup of my Tlog.
then restore my full back and then my tlog stopat=8.44 am

Now my doubt is
1.from where this 10000 records data are fetched from?is it from tlog or from ldf?
2.how sql engine will know the inserted data should be in mdf? means how Tlog is working internally to keep track this record?Like DCM in differential backup is there any algorithm is maintained?

THIS IS CASE 2:
1. restored my full back and then my tlog stopat=8.34 am

so want all the data i have updated will be rolled isn't it.

now my doubt is:
1.how sql engine get the old data or from where this old data is maintained and deleted the inserted value?
0
Comment
  • 9
  • 7
  • 6
22 Comments
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 36514027
tlog IS the ldf file...and yes, that's where the records are until you perform a checkpoint of the database.  SQL knows that the data should be in the mdf because they are marked as committed.

If you restored your full backup and then a tlog with stopat=8:34 your updated data isn't going to be in the database because you updated at 8:35.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 36515362
Tlog is the ldf file?i didnt get it?

>>If you restored your full backup and then a tlog with stopat=8:34 your updated data isn't going to be in the database because you updated at 8:35.<<

nope! I will put in same other way...

I am restoring my full backup to different instance which was taken @8.00 am
then i am restoring my tlog backup @ 8.44 just before the time where 10000 rows got deleted.
then now from where will the deleted data will come from ? Is it from LDF orTRN?

If it' coming from TRN,then what is the use of ldf?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 36516447
>>now from where will the deleted data will come from ? Is it from LDF orTRN?<<
Just think about it for a minute.  You are restoring from a Transaction Log backup (presumably that has an extension of TRN).  Where do you think it comes from?

Let me put it to you another way.  Supposing you had to do a point-in-time restore after the server hard drvie crashed?  In that case there is no data or Transaction Log file, why would this be any different.

>>If it' coming from TRN,then what is the use of ldf?<<
Again assuming that TRN is the extension to your Transaction Log backup and LDF is the extension to your Transaction Log.  Then the answer is quite simple.  The Transaction Log is an integral part of the database, lose it and you risk at best loss of data at worst a corrupt database.  The Transaction Log backup is (you guessed it) a backup (not a copy) of the Transaction Log and is used to restore your database.

Other than suggestion some quiet time studying SQL Server BOL I am not sure if I can explain it any better.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 36516574
The ldf file is your transaction log (tlog).

If you restore a full backup which contains all committed transactions up to 8:00am and then you restore a tlog backup up to 8:44am then there IS NO deleted data yet.  You didn't do the delete until 8:45am.

If you want to know where the 10,000 rows are stored that you WILL BE deleting at 8:45am then answer is "that depends".  If they were committed rows BEFORE your 8:00am full backup then they will be in the mdf file.  If the transactions were not committed until after 8:00am then they will be in the ldf file.

You may want to define for us what you mean when you say LDF, TRN and TLOG as your definition might not be correct.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 36516756
tlog-Tail Log backups are completed when the database is in Full or Bulk Logged recovery prior to a database restoration to capture all transaction log records that have not yet been backed up.

.trn files-A transaction log is a sequential record of all changes made to the database while the actual data is contained in a separate file. The transaction log contains enough information to undo all changes made to the data file as part of any individual transaction.
The log records the start of a transaction, all the changes considered to be a part of it, and then the final commit or rollback of the transaction.

Okay, can anyone explain how the LDF works and useful in sql?
I am getting confused with .trn files and .ldf files?
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 36516806
Ok...now I know what you're talking about.  When you say tlog to most DBA's that means a transaction log, not a tail log backup.

With SQL Server your transaction log file is a .ldf, not a .trn.  I've never seen a SQL Server transaction log with a .trn extension in my 16+ years with SQL Server.

The .ldf file, the transaction log, holds any transactions that have happened since the last full backup or CHECKPOINT.

You asked: "I am restoring my full backup to different instance which was taken @8.00 am
then i am restoring my tlog backup @ 8.44 just before the time where 10000 rows got deleted.
then now from where will the deleted data will come from ? Is it from LDF orTRN?"

My answer still stands as there is no .trn file in the picture.  Do you actually have files with .trn extension on your server?  If so, I suspect that they were transaction log backups, which normally would have a .bak extension.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 36517132
okay please correct me if i am wrong...

1.all log data's are stored in the LDF file after the recent checkpoint.
2.Until the next Tail LOG backup or differential backup or full backup ,all the data's after recent checkpoint will be LDF file.
3.once Tail LOG backup is taken the data's are flushed from LDF file to .bak file or .trn files
4. in case of differential backup or full backup data are flushed from LDF to MDF file.

5.So basically trn files are backup of our ldf file ie log data.
6.Since ldf frequently flushes the data to mdf we are assigning  the initial file size of ldf always lesser than mdf file.

0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 36517520
1.  After a CHECKPOINT data is stored in the mdf, not the ldf.
2.  Any data changes AFTER a CHECKPOINT are in the ldf, until the next full backup or CHECKPOINT.  Log backups and differential backups don't have any effect on what is in the ldf.
3.  When a log backup occurs no data is flushed from it.  The .ldf is just marked to allow reuse.  However, the data in your tail log backup is what WAS marked as committed in the ldf.
4.  Differential backup has no effect on the ldf or mdf file.  Yes, with a full backup, all committed transactions in the ldf are written to the mdf.

5.  If you are naming your log backups with a .trn extension, then yes, that is what was in your ldf file.
6.  Sizing your ldf files is more art than science.What is in an .ldf file only makes it into an .mdf when you do a full backup or CHECKPOINT.  If your database is very busy with lots of updates, inserts, and/or deletes then you might have a fairly large .ldf file and it could be completely normal.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 36544391
>>all log data's are stored in the LDF file after the recent checkpoint.<<
>>after a CHECKPOINT data is stored in the mdf, not the ldf<<
The way i meant was wrong.It will be in mdf only,but the transaction will not be cleared from ldf.Am i right?

1.I like to know how the ldf space is cleared in frequent basis.


For example:I HAVE FULL BACKUP once in a day.

Considering the worst case,I dont have any regular transaction log backup or differential backup.
And suppose if hard drive holds ldf and mdf crash completely,then i will be able to restore only up my last full backup.

So in this case, can i consider till next full back up all the transactions are stored in ldf also,even after committing data to mdf file.

Now I consider every 5 mins once 9GB of transaction data are happening in my sql box and if my harddrive contain ldf file has only 150 GB.Assuming 9 GB of data got deleted from mdf file and after 20 mins I came to know that deleted data needs to restored again.

Now I like to clear my doubts,
2.How sql engine is able restore the delete data and from where this deleted data are coming from?
[recovery model is Full]

considering:
3.Assuming the deleted the data stays in ldf file and  9gb of transaction is happening every 5 minutes which is huge,ldf keeps on growing,which will make the disk drive full within 1.5 hours.
So in this case if i take tail log backup with .trn,will it make the drive free which containing ldf file.

4.Or only full back up will free up the space in ldf file?

Note: only ldf file is placed in a separate drive (eg:O:\ drive)

I know above case given is not good practice to follow,but understanding worst case will help us to get the concept more.
0
 
LVL 17

Assisted Solution

by:Chris Mangus
Chris Mangus earned 250 total points
ID: 36544866
You really only need to be concerned about the ldf if you are in FULL recovery mode.

If you are in FULL recovery mode ldf is "cleared up" when you do a full backup, a transaction log backup, or a CHECKPOINT command is issued.

Even in FULL recovery mode, committing data doesn't write it to the mdf.  It just marks it as committed.  The only thing that causes data to move from ldf to mdf is a CHECKPOINT.  Incidentally, doing a full backup or transaction log backup also makes a CHECKPOINT happen.

If you recovery mode is FULL and you are only taking full backups once a day you will only be able to restore up to the point of your full backup.  It sounds like you are relying on being able to take tail log backups if disaster strikes and that's not a good way to do it.  Why not just add some transaction log backups?  Personally, on most systems that I administer in FULL recovery mode, I do a full backup once a day, a differential backup 12 hours after the full backup, and transaction log backups every 2 hours in between.

For your question 2, if you recovery model is FULL and you aren't doing transaction log backups you are only going to be able to recover data up to the time you took the full backup, unless you are able to take a tail log backup.

For your question 3, backing up a transaction log does not free up space, it only marks the transaction log space as available to write to again.  The idea here is that if a transaction log expands to 9gb then it needs that space.  If you take a log backup and manually shrink the ldf you are going to take a performance hit the next time the ldf expands again, and, you're going to have a fragmented ldf file.  Best practice is to create the ldf file as big as you think it will grow and leave it alone.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36546508
>>If you are in FULL recovery mode ldf is "cleared up" when you do a full backup, a transaction log backup, or a CHECKPOINT command is issued.<<
You may want to double check that.  In Full Recovery Model, the only time the Transaction Log is truncated is after a Transaction Log backup, if a CHECKPOINT has occurred.  In other words, if you are in Full Recovery Model you have to do Transaction Log Backups. Period.

But it is all covered in great detail in SQL Server BOL:
Transaction Log Truncation
http://technet.microsoft.com/en-us/library/ms189085.aspx
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 36547324
>>backing up a transaction log does not free up space, it only marks the transaction log space as available to write to again.<<
Okay,I understood as all the committed transaction pages from LDF will not free up the space,where it just mark those pages as committed.And if any new transaction needs space,it will overwrite the pages used by committed transaction pages earlier.

If i am not concern of disaster failure,do i need take frequent of transactional log backup?In my case ,can i disable the transactional backup used in maintenance plan?

Here I not concern of best practice:Considering the worst practice
I am putting forward this question because,whenever transaction happens it will commit the pages to mdf,and mark those pages as committed in ldf. As per the calculation ldf will be always have some space to write new transaction,overwriting existing transaction pages which are marked as committed.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36548626
>>can i disable the transactional backup used in maintenance plan?<<
No.  If you unable or incapable of doing Transaction Log backups then you should not be using Full Recovery Model.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 36548731
>>backing up a transaction log does not free up space, it only marks the transaction log space as available to write to again.<<
Okay,I understood as all the committed transaction pages from LDF will not free up the space,where it just mark those pages as committed.And if any new transaction needs space,it will overwrite the pages used by committed transaction pages earlier.

1. did i understood correct?

>>can i disable the transactional backup used in maintenance plan?<<
No.  If you unable or incapable of doing Transaction Log backups then you should not be using Full Recovery Model.

2. why i shouldn't disable the transactional log back up?
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 36550510
acperkins - I seems to remember a presentation by Brad McGehee in the past where he said a CHECKPOINT always accompanies a full backup or transaction log backup.  So in essence, CHECKPOINT is the process that drives it all.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 36550684
You did understand correctly.

I agree with acperkins, if you aren't going to do log backups you should be in SIMPLE recovery mode.  it sounds like you're trying to rely on being able to take a tail log backup to do point-in-time recovery in the event of a crash.  That's a pretty bad way to try and ensure your ability to to point-in-time recovery.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36552800
>> why i shouldn't disable the transactional log back up?<<
Because as I have explained, if you don't you will very run out of space very fast.  Here it goes again:
In Full Recovery Model, the only time the Transaction Log is truncated is after a Transaction Log backup

In other words, don't follow my advice, leave your database in Full Recovery Model and don't do Transaction Log backups and watch how the Transaction Log continues to grow until you run out of space.

Just make sure your have your résume updated, as you may need to look for a new job.

VIVEKANANDHAN_PERIASAMY,
It is time you did some homework and by that I mean, open some books/articles and study how a Transaction Log works in SQL Server.  You have asked the same question over and over again in a thousand ways.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36552820
cmangus,

>>I seems to remember a presentation by Brad McGehee in the past <<
Then you must have misunderstood. If you like I can ask Brad to clarify what he meant.  But in any case, the subject is fully covered in the link I posted from SQL Server BOL.  But here are the relevant points:

Except when delayed for some reason, log truncation occurs automatically as follows:
Under the simple recovery model, after a checkpoint.
Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup. For more information, see "Log truncation under the full and bulk-logged recovery models," later in this topic.

...

Under the full recovery model or bulk-logged recovery model, the inactive part of the log cannot be truncated until all its log records have been captured in a log backup. This is needed to maintain the log chain—a series of log records having an unbroken sequence of log sequence numbers (LSNs). The log is truncated when you back up the transaction log, assuming the following conditions exist:


A checkpoint has occurred since the log was last backed up. A checkpoint is essential but not sufficient for truncating the log under the full recovery model or bulk-logged recovery model. After a checkpoint, the log remains intact at least until the next transaction log backup.

For more information, see Checkpoints and the Active Portion of the Log.
No other factor is preventing log transaction.

Generally, with regular backups, log space is regularly freed for future use. However, various factors, such as a long-running transaction, can temporarily prevent log truncation. For more information, see Factors That Can Delay Log Truncation.
The BACKUP LOG statement does not specify WITH COPY_ONLY.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 36552853
Yes, please ask Brad to clarify.  I found a .ppt on his website that almost explicitly says a CHECKPOINT occurs with a transaction log backup.  It's not relevant to the op but I would like to clarify my understanding if I am missing something.

Personally, I trust Brad and most of the known MVP's a lot more than BOL which has been shown to be wrong many times.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36555277
>>I found a .ppt on his website that almost explicitly says a CHECKPOINT occurs with a transaction log backup. <<
And I am not arguing that point.  In fact I stated:
In Full Recovery Model, the only time the Transaction Log is truncated is after a Transaction Log backup, if a CHECKPOINT has occurred.
Where I disagree with you is that a Full backup truncates the Transaction Log or to put it in your words:
"If you are in FULL recovery mode ldf is "cleared up" when you do a full backup".

>>I trust Brad and most of the known MVP's<<
Ouch!  I guess the unknown ones don't count.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 36561650
I understand what you're saying now.  My intent was to show that any committed transactions will be in the mdf.  I wrote it pretty poorly.


>>I trust Brad and most of the known MVP's<<
Ouch!  I guess the unknown ones don't count.

If you're an MVP, please don't take it personally.  I do have direct experience with a couple MVP's who didn't know SQL from a hole in the ground; I couldn't figure out how they were ever invited.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36561835
>>If you're an MVP, please don't take it personally.<<
I didn't.  As you can probably guess, I have a fairly thick skin.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now