Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2007
    Posts
    41

    Unanswered: Bulk logged Recovery Model

    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!

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by SQL Server 2005 Books Online
    the bulk-logged recovery model minimally logs bulk operations (though fully logging other transactions).
    bulk operations is also minimally logged in Simple recovery model
    i.e. for bulk operations only the allocation of extents is logged
    Let's assume your bcp commits in batches of 30000 records
    And lets assume that while inserting the second batch your server unexpectedly reboots.
    Now the recovery phase will know which allocated pages was committed and which once not. So you will see your committed data.
    Quote Originally Posted by frank.svs
    what is the point in taking the T-log backup as in case of Bulk logged Recovery Model
    Quote Originally Posted by SQL Server 2005 Books Online
    a transaction log backup captures both the log and the results of any bulk operations performed
    That is why:
    Quote Originally Posted by SQL Server 2005 Books Online
    Under the bulk-logged recovery model, backing up a log that contains bulk-logged operations requires access to all data files in the database. If the data files are not accessible, the final transaction log cannot be backed up and all committed operations in that log are lost.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •