Re: truncate transaction log
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 07/10/04
- Next message: Dan: "Re: A lot of sp_cursorfetch statements in profiler"
- Previous message: Dan: "A lot of sp_cursorfetch statements in profiler"
- In reply to: Arne Adams: "Re: truncate transaction log"
- Next in thread: Arne Adams: "Re: truncate transaction log"
- Reply: Arne Adams: "Re: truncate transaction log"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 10 Jul 2004 15:55:58 -0400
> I thought that only the bulk_logged operations are not recoverable but
anything
> prior and after that would be recoverable.
> If this was true I would not need to issue a full backup after the
bulk_logged
> operation.
Bulk logged operations are recoverable but only at the entire log level. It
is my understanding (and it's been a while so I am a little fuzzy<g>) that
once a Bulk Logged event has occurred, even if you switch back to Full
recovery mode you must do a full backup to allow point in time recovery
again. This is a section from BOL:
In addition, the Bulk-Logged Recovery model only allows the database to be
recovered to the end of a transaction log backup when the log backup
contains bulk changes. Point-in-time recovery is not supported.
In Microsoft® SQL ServerT 2000, you can switch between full and bulk-logged
recovery models easily. It is not necessary to perform a full database
backup after bulk copy operations complete under the Bulk-Logged Recovery
model. Transaction log backups under this model capture both the log and the
results of any bulk operations performed since the last backup.
This states you don't need the Full backup but I think it is referring to
only if you care about recovery at the entire log level and not point in
time.
> > When you say that the full backup would spoil the
> > performance gain what do you mean?
> What if I programmatically have to do bulk insertions and after the last
of them
> have to start a full backup before I can continue to work with the
database
> would'nt that effect the performance? In general is bulk_logged meant to
be uses
> this way:
> - issue a bulk_logged operation
> - issue a full backup
> - continue working?
There is no reason you can't continue working. There are several ways to
kick off a backup. One of which is to simply use sp_start_job. It returns
control immediately.
> > And when you
> > issue a bulk logged operation and then issue the next log backup it will
> > make a huge log backup anyway.
> Why that?
Here is how a bulk logged operation such as BCP of 1 million rows works.
As the rows get inserted into a table they fill up pages and after 8 pages
they create a new extent and start filling the pages on that extent. A
normal fully logged operation will place information into the tran log file
for each row on an individual basis. This information is all the data for
each row and any extra metadata needed to redo or roll back the tran. If
you have 1 million rows there will be 1 million entries written to the log.
A minimally logged operation only logs the ID of any extents that have been
affected by the operation. So if the million rows created 100 new extents
there is only 100 entries added with just the ID of those extents. No
actual data gets written to the log. (this is mainly why a point in time
restore is not feasible). So this is great as it makes the tran log file
very small and has little overhead compared to a fully logged operation.
But since your in Bulk Logged recovery mode that means you want to recover
at some point so when the log backup is taken it goes and reads the entire
extent from the database for each extent ID that was logged initially and
places this in the log file backup. So your log file backup will contain all
1 million rows in the form of all the extents they changed. That way if you
restore it they just place the whole extent back and not each and every row
as would normally happen. This process makes for a longer and larger log
backup compared to the amount of what is actually logged to the tran log.
By the way in order for a minimally logged operation to occur you need
several conditions as outlined in BOL under "minimally logged bulk copy".
If any one of these are not met the entire operation is fully logged anyway
regardless of the recovery mode. From the sounds of it I am guessing you
not really getting a minimally logged operation anyway.
-- Andrew J. Kelly SQL MVP "Arne Adams" <arne.adams@t-online.de> wrote in message news:ccpb37$rvr$02$1@news.t-online.com... > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> schrieb im Newsbeitrag > news:e32Lf$pZEHA.2844@TK2MSFTNGP12.phx.gbl... > > Once you do a bulk logged operation in the database you can no longer do a > > point in time restore. That is one of the main advantages of being in full > > recovery mode. The only way to get this ability back is to issue a full > > backup after the bulk logged operation. > In my current application I have to bulkinsert frequently a lot of data (csv > file sizes of 500 MB when one month is covered but chances are that we will have > to handle one years data in the future). This specific operation does not need > to be recoverable. I did find the hint in a german book that a bulk_logged > operation might perform faster (and this is especially relevant when I do that > for testing on my laptop). > I thought that only the bulk_logged operations are not recoverable but anything > prior and after that would be recoverable. > If this was true I would not need to issue a full backup after the bulk_logged > operation. > > > The other reason I stated that is > > you said you did a Backup Log with Truncate_Only. This also invalidates the > > log chain and makes restores from the log files imposable at that point. > This is clear to me and no problem. > > > > When you say that the full backup would spoil the > > performance gain what do you mean? > What if I programmatically have to do bulk insertions and after the last of them > have to start a full backup before I can continue to work with the database > would'nt that effect the performance? In general is bulk_logged meant to be uses > this way: > - issue a bulk_logged operation > - issue a full backup > - continue working? > > > And when you > > issue a bulk logged operation and then issue the next log backup it will > > make a huge log backup anyway. > Why that? > > > > > -- > Regards, > > Arne > >
- Next message: Dan: "Re: A lot of sp_cursorfetch statements in profiler"
- Previous message: Dan: "A lot of sp_cursorfetch statements in profiler"
- In reply to: Arne Adams: "Re: truncate transaction log"
- Next in thread: Arne Adams: "Re: truncate transaction log"
- Reply: Arne Adams: "Re: truncate transaction log"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
