October 16, 2015 at 2:13 am
Hi expert,
when we use Simple recover model we are not able to take Transaction log backup, but my doubt is, if we are not able to take Transaction Log backup,
what is the use LDF file in Simple recovery model data base.
What type of data we are suppose to store in LDF file.
Is there any possibility to remove LDF and maintain only MDF file from Simple recovery model.
Thanks in advance...
October 16, 2015 at 2:16 am
https://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx#id0060041"> https://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx#id0060041
A must read - hopefully it can offer some answers for you.
October 16, 2015 at 3:28 am
mahi123 (10/16/2015)
what is the use LDF file in Simple recovery model data base.
Transaction rollbacks, database recovery after a shutdown, database integrity, minor things like that.
Is there any possibility to remove LDF and maintain only MDF file from Simple recovery model.
Would you really like a database where an insert/update/delete failure would require the entire database to be restored from the last good backup? Because that's what a database without logging would have to behave like.
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
October 16, 2015 at 4:00 am
Hello
what is the use LDF file in Simple recovery model data base.
RDBMS will always need some "temporary" space to keep "notes" about the progress of the transaction. In case transaction fails at some point those "notes" will be used to revert to the state before the transaction. In SQL Server those "notes" are stored in the log.
If transaction fails you no longer need those notes so they are discarded. If transaction succeeds you can keep them (full recovery mode) or discard as well (simple recovery).
If you have all the "notes" for all transactions you can re-do all the changes in the database (which is what happens when you do the restore)
What type of data we are suppose to store in LDF file.
SQL Server will manage it for you 🙂
Is there any possibility to remove LDF and maintain only MDF file from Simple recovery model.
AFAIK - no.
Why do you want to get rid of LDF file?
regards
ptr
October 16, 2015 at 4:19 am
Piotr ER (10/16/2015)
If transaction fails you no longer need those notes so they are discarded. If transaction succeeds you can keep them (full recovery mode) or discard as well (simple recovery).If you have all the "notes" for all transactions you can re-do all the changes in the database (which is what happens when you do the restore)
Not quite. Log records are never discarded, even if the transaction fails. They're needed to roll the transaction back, they may be needed for database recovery. It's only when the entire virtual log file is no longer needed for anything (active transaction, database backup, replication, checkpoint, log backup if in full/bulk-logged recovery) that the VLF will be marked reusable.
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
October 16, 2015 at 4:51 am
Ok, i should write "when transaction fails and rollback is completed" :). But when rollback is done - you no longer need log records. Of course "discarded" does not mean that log is immediately cleared but for the user it's an implementation detail.
I think that the main point for our colleague is that database will always need a log to do some magic stuff and it does not matter what recovery mode and backup strategy we have
regards
ptr
October 16, 2015 at 4:56 am
Piotr ER (10/16/2015)
Ok, i should write "when transaction fails and rollback is completed" :). But when rollback is done - you no longer need log records.
That's still not true in general. They may still be needed for database recovery even after the rollback has completed.
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
October 16, 2015 at 5:35 am
Hi Kindly provide answer, My data base is Simple recovery mode, which is not usefull to take transactional log back up. but still In Simple recovery model we can use LDF files, in this simple recovery model what type of data stored in LDF files
October 16, 2015 at 5:43 am
Same as in bulk logged and full recovery. Log records describing changes made to the database. The main difference with Simple recovery is how long the log records are retained, not what's in the transaction log.
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply