November 28, 2008 at 9:46 am
Hi
1.I have data base with MDF and LDF
2.I Inserted two rows into the Database
3.I detach the Db and deleted LDf
4.Attached MDF only automatically Ldf also created
5...................Then In the db that two rows also displayed.
If i insert any rows that rows will be stored in LDF or MDF ?
November 28, 2008 at 9:52 am
Both. First, immediately, in the LDF. Then, later, in the MDF (here "later" is a short time: microseconds to seconds). The LDF storage is only "temporary" however and it will be overwritten sooner or later, sometime after your transaction completes (here "later" can be a very long time: minutes to days).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 28, 2008 at 9:53 am
Thnks
So we lost ldf file---we can't loss the data
is it?
November 28, 2008 at 10:05 am
Lee From Bangalore (11/28/2008)
ThnksSo we lost ldf file---we can't loss the data
is it?
It is not clear what you are asking here, because you are mixing your tenses.
Are saying that you have lost the LDF, and you need to know how to recover your data?
Or are saying that you have lost the LDF, and you need to be sure that you do not now lose the MDF?
Or are saying that you need to be sure that you do not lose the LDF?
Or are saying that you need to be sure that if you do lose the LDF, then you will not lose your data?
Etc., etc...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 28, 2008 at 10:07 am
It depends. If the transaction is open and you stop SQL and delete the transaction it will "lose" the data in the transaction log (because you just deleted it). 🙂
Are you thinking about what to backup to safeguard your data? :hehe:
PS. I hope this database is a test one, deleting files from live systems tends to make people a bit tetchy. :w00t:
November 28, 2008 at 10:11 am
What I mean is
If any insertion is done that will be stored first to LDF and next to MdF
if ldf is crased what we will lost?
I need exact what will stored in LDF and its purpose?
November 28, 2008 at 10:24 am
What is in the ldf file
If the database is in full mode:
Every transaction is first written to the log then (on commit transaction) committed to the database (mdf file). Unless you backup the transaction log, this record will grow forever until it consumes all your disk space.
If the database is in simple mode
Same as above but it flushes the committed transactions out of the log as it goes keeping your log file small(er).
What is lost if the ldf file crashes?
Ignoring the fact that ldf files don't usually crash unless something bad happens: You will lose any uncommitted transactions and the record of any committed transactions since the last transaction log backup.
November 28, 2008 at 10:28 am
Thanks for your clarification
Thanking you
November 28, 2008 at 10:28 am
The LDF is what insures that you can recover after a failure, assuming you are not in SIMPLE mode. The information stored in the LDF is the data that is necessary to ROLLBACK a transaction, if requested, and what is needed to be able to Recover your MDF by Rolling forward all of your transactions since your last backup (again, if you are not in SIMPLE mode).
If you lose your LDF, then you cannot continue running and you also cannot guarantee that your MDF is usable at that point so you could well have to restore from your backups (let's say that there is a 50% chance of this, it's actually highly variable, but the more active your database is, the more likely it is that it will be bad). Without your LDF, you would only be able to recover up to your last backup.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 28, 2008 at 1:42 pm
Lee From Bangalore (11/28/2008)
I need exact what will stored in LDF and its purpose?
The ldf is the log of all changes that are made to the database. A change has to be hardened in the transaction log (ie, on disk) before the change is considered complete. The change will also be made to the data pages, but that will be in memory.
If the server crashed, it's the transaction log that's used to get the database to a transactionally-consistent state when SQL restarts.
If a transaction is rolled back, it's the records of the change in the transaction log that are used to undo the changes
The transaction log is what ensures transactional consistency and durability in SQL (C and D in ACID, the requirements for a relational database)
You should never delete the transaction log file. At best doing so breaks the log chain and means it's impossible to do a point-in-time recovery until another full backup is run. Worst case it results in a suspect database.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply