Bulk-Logged Recovery question

Dear all
I have read the BOL section on this and would really appreciate it if
someone confirmed my understanding of this for me with the following
scenario
Imagine that I have a database that has two sets of tables:
The first set contain what could best be described as the product portfolio,
i.e. data about products for sale. These tables contain many rows that are
only ever updated/inserted by using BULK INSERT operations.
The second set of tables contain information about orders. These tables are
updated as someone places an order (web or telesales front end).
If I take a full backup of the database and then set the transaction log to
backup every 15 minutes, then I'd expect one enormous *.BAK file and
sequential set of relatively small *.TRN files, whose size was approximately
proportional to the number of order entered.
However, I then bulk insert > 1 million rows into the product portfolio
tables. The recovery option for the database is set to BULK LOGGED; the
operation is therefore extremely fast and the only information saved to the
transaction log concerns the pages/extents affected.
However, what happens the next time the transaction log is backed up? Is
the TRN file enormous because it contains all the data that was inserted in
the BULK INSERT step? Or, is the TRN file the normal size because it was
effectively unaware of the BULK INSERT occurring? I'm unsure of this.
Assuming that the former is true (i.e. that the TRN is enormous), then I
presume that it would be sensible to have the product portfolio tables in
one database that was only backed up now and again and have the
orders-tables in another database that had FULL backup of the transactions?
Would this be the best scenario?
Thanks
GriffYes the transaction log backup will be big, those extents modified by the
BULK INSERT will be recorded in the Bulk Changed Map (BCM) page(s). When you
backup the log, those extents will be included. Since you have 2 disparate
recovery requirements, it would make sense to separate them into two
databases. This would allow point in time recovery for your orders (assuming
you set that database to FULL recovery) which would not be possible in the
BULK LOGGED recovery model and you could use SIMPLE recovery for your
product database.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Griff" <Howling@.The.Moon> wrote in message
news:ecmLg%231jEHA.484@.TK2MSFTNGP10.phx.gbl...
> Dear all
> I have read the BOL section on this and would really appreciate it if
> someone confirmed my understanding of this for me with the following
> scenario
> Imagine that I have a database that has two sets of tables:
> The first set contain what could best be described as the product
> portfolio,
> i.e. data about products for sale. These tables contain many rows that
> are
> only ever updated/inserted by using BULK INSERT operations.
> The second set of tables contain information about orders. These tables
> are
> updated as someone places an order (web or telesales front end).
> If I take a full backup of the database and then set the transaction log
> to
> backup every 15 minutes, then I'd expect one enormous *.BAK file and
> sequential set of relatively small *.TRN files, whose size was
> approximately
> proportional to the number of order entered.
> However, I then bulk insert > 1 million rows into the product portfolio
> tables. The recovery option for the database is set to BULK LOGGED; the
> operation is therefore extremely fast and the only information saved to
> the
> transaction log concerns the pages/extents affected.
> However, what happens the next time the transaction log is backed up? Is
> the TRN file enormous because it contains all the data that was inserted
> in
> the BULK INSERT step? Or, is the TRN file the normal size because it was
> effectively unaware of the BULK INSERT occurring? I'm unsure of this.
> Assuming that the former is true (i.e. that the TRN is enormous), then I
> presume that it would be sensible to have the product portfolio tables in
> one database that was only backed up now and again and have the
> orders-tables in another database that had FULL backup of the
> transactions?
> Would this be the best scenario?
> Thanks
> Griff
>|||Griff,
The point of BULK LOGGED is that all the details are not logged in the
transaction log so it remains relatively small. However, to make this all
work the extents that were modified are also backed up to the the .TRN file
along with the contents of the transaction log.
Here is an article from SQL Server Magazine that might be helpful in
explaining.
http://tinyurl.com/6hvmo
Russell Fields
"Griff" <Howling@.The.Moon> wrote in message
news:ecmLg%231jEHA.484@.TK2MSFTNGP10.phx.gbl...
> Dear all
> I have read the BOL section on this and would really appreciate it if
> someone confirmed my understanding of this for me with the following
> scenario
> Imagine that I have a database that has two sets of tables:
> The first set contain what could best be described as the product
portfolio,
> i.e. data about products for sale. These tables contain many rows that
are
> only ever updated/inserted by using BULK INSERT operations.
> The second set of tables contain information about orders. These tables
are
> updated as someone places an order (web or telesales front end).
> If I take a full backup of the database and then set the transaction log
to
> backup every 15 minutes, then I'd expect one enormous *.BAK file and
> sequential set of relatively small *.TRN files, whose size was
approximately
> proportional to the number of order entered.
> However, I then bulk insert > 1 million rows into the product portfolio
> tables. The recovery option for the database is set to BULK LOGGED; the
> operation is therefore extremely fast and the only information saved to
the
> transaction log concerns the pages/extents affected.
> However, what happens the next time the transaction log is backed up? Is
> the TRN file enormous because it contains all the data that was inserted
in
> the BULK INSERT step? Or, is the TRN file the normal size because it was
> effectively unaware of the BULK INSERT occurring? I'm unsure of this.
> Assuming that the former is true (i.e. that the TRN is enormous), then I
> presume that it would be sensible to have the product portfolio tables in
> one database that was only backed up now and again and have the
> orders-tables in another database that had FULL backup of the
transactions?
> Would this be the best scenario?
> Thanks
> Griff
>