Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2009-07-01 : 19:18:49
|
| Hi Friends,Small clarification needed on Recovery Models.As per my knowledge, If we choose Simple recovery model, we cannot take Transaction Log backup. Right! and the reason for this whenever 70% of the Log is full , Sql server issues a CKPT and committed txns are written back to .mdf file and that space if freed up and it can be overwritten other txns in the .ldf file. And so for this reason, we dont often see any increase in the Transaction Log. And so for that very reason, i.e. since it is getting overwritten, there is no use of taking the T-Log backup. This is again as per my understanding. pl correct me if am wrong!!!Coming to Bulk logged recovery model, only minimal things are logged inside my Transaction - log i.e. not every operation is logged.and if we do bulk insert operation say (10 million records) am loading, then it wont log all my 10 million operations in T-log. Right! Then my question is , why did Microsoft has provided a T-Log backup in the case of Bulk Logged Recovery Model.Also, if anything failure occurs in the middle of my BCP operation entire thing is ROLLBACK'd. Then what is the point in taking the T-log backup as in case of Bulk logged Recovery Model. How it is going to help me out during my recovery phase?can Anyone help me out in understanding??Any practical scenario will be more helpful!!!Thank You! |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2009-07-02 : 01:38:35
|
| The advantage of using this Bulk Logged Recovery model is that your transaction logs will not get that large if you are doing bulk operations and you have the ability to do point in time recovery as long as your last transaction log backup does not include a bulk operation. If no bulk operations are run this recovery model works the same as the Full recovery model |
 |
|
|
|
|
|
Bulk logged Recovery Model