Bulk Logged Recovery Model

Hello all,
If a database is set to bulk-logged recovery model, does the transaction
log truncates itself (as it does in the simple model) or do you have to
set up a backup tlog job like you do in the full recovery model for it
to truncate itself? (SQL 2000)
Thanks,
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!No it does not truncate as it would in simple.
Also if you are in bulk-logged and do a tran log backup then the data =files must be accessible. Thus in bulk logged mode you cannot rescue the =tail of the log (Using backup log with no truncate) unless the db files =are intact. Full mode will let you do this. Thus there is a difference =in disaster recovery planning.
Mike John
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message =news:OD5tN9duDHA.536@.tk2msftngp13.phx.gbl...
> Hello all,
> > If a database is set to bulk-logged recovery model, does the =transaction
> log truncates itself (as it does in the simple model) or do you have =to
> set up a backup tlog job like you do in the full recovery model for it
> to truncate itself? (SQL 2000)
> > Thanks,
> Raziq.
> > > *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:OD5tN9duDHA.536@.tk2msftngp13.phx.gbl...
> If a database is set to bulk-logged recovery model, does the transaction
> log truncates itself (as it does in the simple model) or do you have to
> set up a backup tlog job like you do in the full recovery model for it
> to truncate itself? (SQL 2000)
While a database is set to bulk-logged the transaction log won't it won't
truncate itself, it still allows transaction log recoverability while
minimally logging bulk import activity. You're correct that you'd need to
setup a backup transaction log task. For more information see BOL 'Using
Recovery Modes' and 'Bulk-Logged Recovery'
Steve

Bulk logged or Simple

Hi,
We are using Simple recovery model for one of our Datamart
database. We choose simple model since we don't want to
worry about backup the transaction log as we can rebuild
datamart from oltp database and also to have effective use
of minimal-logging (we are using select..into and bulk
insert)....
When i was testing datamart database i found that with
Simple recovery model sql server do minimal logging..
but i am reading earlier posts and it is mentioned that
only with bulk-logged recovery model sql server do minimal
logging..
Is simple recovery model do logging same as Bulk logged
(minimal logging for SELECT..INTO , BULK INSERT etc) or
full(complete logging)'
Thanks
--Harvinderu specified:
>no bulk logged operations are logged in case
>of simple recovery model.
Is that means in simple recovery model SELECT..INTO is
minimally logged or fully logged?
I tested it using dbcc sqlperf(logspace) and i can see it
is minimally logged in Sinple mode.
Thanks
--Harvinder
>--Original Message--
>>only with bulk-logged recovery model sql server do
minimal
>>logging..
>The correct statement is in case of bulk logged recovery
model bulk logged
>operations like bulk insert/bcp are minimally logged
>In case of simple recovery model transaction log size
will be manageable
>because transaction log will be truncated
>each time a checkpoint occurs. no bulk logged operations
are logged in case
>of simple recovery model.
>
>Refer to following topics under BOL
>"Selecting a Recovery Model"
>"simple recovery"
>"bulk-logged recovery"
>"Selecting a Recovery Model"
>- Vishal
>"harvinder" <hs@.metratech.com> wrote in message
>news:031c01c37bb9$8e878c40$a301280a@.phx.gbl...
>> Hi,
>>
>> We are using Simple recovery model for one of our
Datamart
>> database. We choose simple model since we don't want to
>> worry about backup the transaction log as we can rebuild
>> datamart from oltp database and also to have effective
use
>> of minimal-logging (we are using select..into and bulk
>> insert)....
>> When i was testing datamart database i found that with
>> Simple recovery model sql server do minimal logging..
>> but i am reading earlier posts and it is mentioned that
>> only with bulk-logged recovery model sql server do
minimal
>> logging..
>> Is simple recovery model do logging same as Bulk logged
>> (minimal logging for SELECT..INTO , BULK INSERT etc) or
>> full(complete logging)'
>> Thanks
>> --Harvinder
>
>.
>|||yes, in case of simple recovery model bulk logged operations are minimally
logged. And transaction log will be truncated at the checkpoint. These
operations are fully logged only in case of "Full recovery" model.
- vishal
"harvinder" <hs@.metratech.com> wrote in message
news:04f201c37bbe$91c511c0$a101280a@.phx.gbl...
> u specified:
> >no bulk logged operations are logged in case
> >of simple recovery model.
> Is that means in simple recovery model SELECT..INTO is
> minimally logged or fully logged?
> I tested it using dbcc sqlperf(logspace) and i can see it
> is minimally logged in Sinple mode.
>
> Thanks
> --Harvinder
>
> >--Original Message--
> >>only with bulk-logged recovery model sql server do
> minimal
> >>logging..
> >
> >The correct statement is in case of bulk logged recovery
> model bulk logged
> >operations like bulk insert/bcp are minimally logged
> >
> >In case of simple recovery model transaction log size
> will be manageable
> >because transaction log will be truncated
> >each time a checkpoint occurs. no bulk logged operations
> are logged in case
> >of simple recovery model.
> >
> >
> >Refer to following topics under BOL
> >
> >"Selecting a Recovery Model"
> >"simple recovery"
> >"bulk-logged recovery"
> >"Selecting a Recovery Model"
> >
> >- Vishal
> >"harvinder" <hs@.metratech.com> wrote in message
> >news:031c01c37bb9$8e878c40$a301280a@.phx.gbl...
> >> Hi,
> >>
> >>
> >> We are using Simple recovery model for one of our
> Datamart
> >> database. We choose simple model since we don't want to
> >> worry about backup the transaction log as we can rebuild
> >> datamart from oltp database and also to have effective
> use
> >> of minimal-logging (we are using select..into and bulk
> >> insert)....
> >> When i was testing datamart database i found that with
> >> Simple recovery model sql server do minimal logging..
> >> but i am reading earlier posts and it is mentioned that
> >> only with bulk-logged recovery model sql server do
> minimal
> >> logging..
> >>
> >> Is simple recovery model do logging same as Bulk logged
> >> (minimal logging for SELECT..INTO , BULK INSERT etc) or
> >> full(complete logging)'
> >>
> >> Thanks
> >> --Harvinder
> >>
> >
> >
> >.
> >

BULK LOGGED Operations

I'm seeing some odd behavior:
When applying an initial snaphot to a subscriber, I keep getting errors
saying the log for the database is full and a rollback occurs.
The weird tying is that I have the recovery model set to BULK LOGGED and I
also created a job that dumps the transaction log every two minutes, yet the
log keeps filling up supposedly.
Not to mention the log is 15GB in size...
Anyone ever see something like this?
Thanks!
You may want to try running the distribution agent with a -BcpBatchSize of
2^31 - delta (pick delta so the resulting batch size is > number of rows in
your table) and see if the log still grows to a large size. Please be
forewarned that you may see the "Agent is suspect" message when applying the
bcp file with a large bcp batch size since the distribution agent will not be
able to post a progress message until the entire table is bulk-copied in and
you should be ok as long as the log doesn't grow uncontrollably.
Do you know if there are any nonclustered indexes created on the table
before the BCP? If only the clustered was created before the bcp, do you know
if a load ordering hint was used? (Use SQL Profiler to find out) If the
distribution agent is creating nonclustered indexes before the BCP, you may
want to remove the nonclustered indexes in the idx scripts before applying
the snapshot and manually create them afterwards.
I have a feeling that you have enabled the "do not lock tables during
snapshot" for the publication but please let me know if this is not the case.
One last thing that you may want to try is to change the -MaxBcpThreads
setting of the distribution agent to > 1. The idea is that if you have
enabled the "do not lock table during snapshot" publication option, setting
-MaxBcpThreads > 1 may allow your biggest table to be bulk-copied by an
auxiliary thread that is not part of the transaction that the distribution
agent creates on the main thread when you enable the "do not lock.. " option.
HTH
-Raymond
"A. Robinson" wrote:

> I'm seeing some odd behavior:
> When applying an initial snaphot to a subscriber, I keep getting errors
> saying the log for the database is full and a rollback occurs.
> The weird tying is that I have the recovery model set to BULK LOGGED and I
> also created a job that dumps the transaction log every two minutes, yet the
> log keeps filling up supposedly.
> Not to mention the log is 15GB in size...
> Anyone ever see something like this?
> Thanks!
|||The table it keeps dying on has 3,422,613 rows in it...
Are you suggesting that I make the commit batch size something like 4,000,000?
And you're right: I did generate the snapshot with the DO NOT LOCK TABLES
option. I've set the MaxBCPThreads to 2.
Does this sound good to you?
Thanks!
"Raymond Mak [MSFT]" wrote:
[vbcol=seagreen]
> You may want to try running the distribution agent with a -BcpBatchSize of
> 2^31 - delta (pick delta so the resulting batch size is > number of rows in
> your table) and see if the log still grows to a large size. Please be
> forewarned that you may see the "Agent is suspect" message when applying the
> bcp file with a large bcp batch size since the distribution agent will not be
> able to post a progress message until the entire table is bulk-copied in and
> you should be ok as long as the log doesn't grow uncontrollably.
> Do you know if there are any nonclustered indexes created on the table
> before the BCP? If only the clustered was created before the bcp, do you know
> if a load ordering hint was used? (Use SQL Profiler to find out) If the
> distribution agent is creating nonclustered indexes before the BCP, you may
> want to remove the nonclustered indexes in the idx scripts before applying
> the snapshot and manually create them afterwards.
> I have a feeling that you have enabled the "do not lock tables during
> snapshot" for the publication but please let me know if this is not the case.
> One last thing that you may want to try is to change the -MaxBcpThreads
> setting of the distribution agent to > 1. The idea is that if you have
> enabled the "do not lock table during snapshot" publication option, setting
> -MaxBcpThreads > 1 may allow your biggest table to be bulk-copied by an
> auxiliary thread that is not part of the transaction that the distribution
> agent creates on the main thread when you enable the "do not lock.. " option.
> HTH
> -Raymond
> "A. Robinson" wrote:
|||Raymond:
Just for the record: it worked like a charm!!!!
A thousand thank you's!!!
"A. Robinson" wrote:
[vbcol=seagreen]
> The table it keeps dying on has 3,422,613 rows in it...
> Are you suggesting that I make the commit batch size something like 4,000,000?
> And you're right: I did generate the snapshot with the DO NOT LOCK TABLES
> option. I've set the MaxBCPThreads to 2.
> Does this sound good to you?
> Thanks!
> "Raymond Mak [MSFT]" wrote:
sql