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 insert, commit and simple recovery mode

Hello,
I have a theoretical question:
If my database is in simple recover mode, and a bulk insert is performed. As
far as I know in simple recovery mode bulk data is not logged. My question is
- after I commit the transaction, is the data written to the disk? Or there
can be a possibility the data is still in memory, but was not actually
written to disk?
I know that in simple recovery mode - the database can ensure the commit
even before it writes updated pages to the data files because at recover time
it can apply all bulk data changes from log.
Regards
Ronen S.
"Ronen Shachar" <Ronen Shachar@.discussions.microsoft.com> wrote in message
news:2D42EE27-115F-4613-A7F2-CA74B609C579@.microsoft.com...
> Hello,
> I have a theoretical question:
> If my database is in simple recover mode, and a bulk insert is performed.
> As
> far as I know in simple recovery mode bulk data is not logged.
This is not really accurate and is a common misconception.
There IS logging occurring, just not to the same level of detail.

> My question is
> - after I commit the transaction, is the data written to the disk? Or
> there
> can be a possibility the data is still in memory, but was not actually
> written to disk?
No. Once a transaction is committed, it's safely on the disk.
(Pick up Kalen Delany's "Inside SQL 2005 - Database Engine" for more
details. (was just reading about this last night.)

> I know that in simple recovery mode - the database can ensure the commit
> even before it writes updated pages to the data files because at recover
> time
> it can apply all bulk data changes from log.
> Regards
> Ronen S.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Bulk insert, commit and simple recovery mode

Hello,
I have a theoretical question:
If my database is in simple recover mode, and a bulk insert is performed. As
far as I know in simple recovery mode bulk data is not logged. My question is
- after I commit the transaction, is the data written to the disk? Or there
can be a possibility the data is still in memory, but was not actually
written to disk?
I know that in simple recovery mode - the database can ensure the commit
even before it writes updated pages to the data files because at recover time
it can apply all bulk data changes from log.
Regards
Ronen S."Ronen Shachar" <Ronen Shachar@.discussions.microsoft.com> wrote in message
news:2D42EE27-115F-4613-A7F2-CA74B609C579@.microsoft.com...
> Hello,
> I have a theoretical question:
> If my database is in simple recover mode, and a bulk insert is performed.
> As
> far as I know in simple recovery mode bulk data is not logged.
This is not really accurate and is a common misconception.
There IS logging occurring, just not to the same level of detail.
> My question is
> - after I commit the transaction, is the data written to the disk? Or
> there
> can be a possibility the data is still in memory, but was not actually
> written to disk?
No. Once a transaction is committed, it's safely on the disk.
(Pick up Kalen Delany's "Inside SQL 2005 - Database Engine" for more
details. (was just reading about this last night.)
> I know that in simple recovery mode - the database can ensure the commit
> even before it writes updated pages to the data files because at recover
> time
> it can apply all bulk data changes from log.
> Regards
> Ronen S.
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Bulk insert, commit and simple recovery mode

Hello,
I have a theoretical question:
If my database is in simple recover mode, and a bulk insert is performed. As
far as I know in simple recovery mode bulk data is not logged. My question i
s
- after I commit the transaction, is the data written to the disk? Or there
can be a possibility the data is still in memory, but was not actually
written to disk?
I know that in simple recovery mode - the database can ensure the commit
even before it writes updated pages to the data files because at recover tim
e
it can apply all bulk data changes from log.
Regards
Ronen S."Ronen Shachar" <Ronen Shachar@.discussions.microsoft.com> wrote in message
news:2D42EE27-115F-4613-A7F2-CA74B609C579@.microsoft.com...
> Hello,
> I have a theoretical question:
> If my database is in simple recover mode, and a bulk insert is performed.
> As
> far as I know in simple recovery mode bulk data is not logged.
This is not really accurate and is a common misconception.
There IS logging occurring, just not to the same level of detail.

> My question is
> - after I commit the transaction, is the data written to the disk? Or
> there
> can be a possibility the data is still in memory, but was not actually
> written to disk?
No. Once a transaction is committed, it's safely on the disk.
(Pick up Kalen Delany's "Inside SQL 2005 - Database Engine" for more
details. (was just reading about this last night.)

> I know that in simple recovery mode - the database can ensure the commit
> even before it writes updated pages to the data files because at recover
> time
> it can apply all bulk data changes from log.
> Regards
> Ronen S.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.comsql

Building a Recovery/Test SQL Server

We have a production SQL 2000 Server and we want to build
an offline-recovery server where we can install
applications, restore databases, and test client
connectivity. I would build this server exactly like our
production server, restore the msdb, model databases,
etc. Would I be better off having this server on a test
lan seperate from my production server, or can I have
both these servers(different names and IPs or course)on
the same network. What would be a better way of
introducing this recovery server, and is this even
possible?
Thanks,
Alex
Hi Alex,
You can have Two separate SQL Servers on the network.
If you are using SQL Server 2000 Enterprise Edition, then you can use the
Log Shipping Feature of the SQL Server to create a Warm Backup. This backup
Server can be used for reports. The users would have read only access to
the Warm Standby Server.
Please refer to the following white paper that has steps to setup Log
Shipping and steps to convert a standby server to a production server, in
case the Production server crashes.
http://support.microsoft.com/default...l%2fcontent%2f
2000papers%2fLogShippingFinal.asp
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

Building a Recovery/Test SQL Server

We have a production SQL 2000 Server and we want to build
an offline-recovery server where we can install
applications, restore databases, and test client
connectivity. I would build this server exactly like our
production server, restore the msdb, model databases,
etc. Would I be better off having this server on a test
lan seperate from my production server, or can I have
both these servers(different names and IPs or course)on
the same network. What would be a better way of
introducing this recovery server, and is this even
possible?
Thanks,
AlexHi Alex,
You can have Two separate SQL Servers on the network.
If you are using SQL Server 2000 Enterprise Edition, then you can use the
Log Shipping Feature of the SQL Server to create a Warm Backup. This backup
Server can be used for reports. The users would have read only access to
the Warm Standby Server.
Please refer to the following white paper that has steps to setup Log
Shipping and steps to convert a standby server to a production server, in
case the Production server crashes.
http://support.microsoft.com/default.aspx?scid=%2fsupport%2fsql%2fcontent%2f
2000papers%2fLogShippingFinal.asp
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

Building a Recovery/Test SQL Server

We have a production SQL 2000 Server and we want to build
an offline-recovery server where we can install
applications, restore databases, and test client
connectivity. I would build this server exactly like our
production server, restore the msdb, model databases,
etc. Would I be better off having this server on a test
lan seperate from my production server, or can I have
both these servers(different names and IPs or course)on
the same network. What would be a better way of
introducing this recovery server, and is this even
possible?
Thanks,
AlexHi Alex,
You can have Two separate SQL Servers on the network.
If you are using SQL Server 2000 Enterprise Edition, then you can use the
Log Shipping Feature of the SQL Server to create a Warm Backup. This backup
Server can be used for reports. The users would have read only access to
the Warm Standby Server.
Please refer to the following white paper that has steps to setup Log
Shipping and steps to convert a standby server to a production server, in
case the Production server crashes.
http://support.microsoft.com/defaul...ql%2fcontent%2f
2000papers%2fLogShippingFinal.asp
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.