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 copy(batch insert) is failed when the mssql backup the database

Hi All,
I have an application to insert data to database by using(bulk copy, batch i
nsert) and this application
will be return an error :
State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC,
bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operati
ons on a database must be serialized. Reissue the statement after the curren
t backup, CHECKA
LLOC, or file manipulation operation is completed.
when the application is in the middle to insert the data into the database a
nd the database backup is started.
Is there any way that the bulk copy and database backup can run concurrently
.
Thanks,
Jodie> Is there any way that the bulk copy and database backup can run
concurrently.
SQL 7 does not allow concurrent bulk copy and backup operations. This is
permitted in SQL 2000, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:C0D5B81E-A83F-48D0-BFF2-618687ACF4F2@.microsoft.com...
> Hi All,
> I have an application to insert data to database by using(bulk copy, batch
insert) and this application
> will be return an error :
> State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL
Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation
(such as CREATE FILE) operations on a database must be serialized. Reissue
the statement after the current backup, CHECKALLOC, or file manipulation
operation is completed.
> when the application is in the middle to insert the data into the database
and the database backup is started.
> Is there any way that the bulk copy and database backup can run
concurrently.
> Thanks,
> Jodie|||Hi Dan,
I got this error message in MSSQL 2000 not SQL 7.0.
Thanks,
Joanne|||Hi, Joanne.
I haven't been able to recreate this problem on my test SQL 2000 server,
although I can get the error under SQL 7. Please post the results of the
following query so that I can make sure we have similar environments.
SELECT *
FROM master..sysmessages
WHERE error = 3023
SELECT @.@.VERSION
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:57D4D054-2A0D-4E4F-A09E-E1AAA1C32B5B@.microsoft.com...
> Hi Dan,
> I got this error message in MSSQL 2000 not SQL 7.0.
> Thanks,
> Joanne
>|||Thanks a lot Dan,
Jodie

bulk copy(batch insert) is failed when the mssql backup the database

Hi All
I have an application to insert data to database by using(bulk copy, batch insert) and this applicatio
will be return an error
State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed
when the application is in the middle to insert the data into the database and the database backup is started
Is there any way that the bulk copy and database backup can run concurrently
Thanks
Jodie> Is there any way that the bulk copy and database backup can run
concurrently.
SQL 7 does not allow concurrent bulk copy and backup operations. This is
permitted in SQL 2000, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:C0D5B81E-A83F-48D0-BFF2-618687ACF4F2@.microsoft.com...
> Hi All,
> I have an application to insert data to database by using(bulk copy, batch
insert) and this application
> will be return an error :
> State 42000, Reason: [Microsoft][ODBC SQL Server Driver][SQL
Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation
(such as CREATE FILE) operations on a database must be serialized. Reissue
the statement after the current backup, CHECKALLOC, or file manipulation
operation is completed.
> when the application is in the middle to insert the data into the database
and the database backup is started.
> Is there any way that the bulk copy and database backup can run
concurrently.
> Thanks,
> Jodie|||Hi Dan
I got this error message in MSSQL 2000 not SQL 7.0
Thanks
Joann|||Hi, Joanne.
I haven't been able to recreate this problem on my test SQL 2000 server,
although I can get the error under SQL 7. Please post the results of the
following query so that I can make sure we have similar environments.
SELECT *
FROM master..sysmessages
WHERE error = 3023
SELECT @.@.VERSION
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jodie Le" <anonymous@.discussions.microsoft.com> wrote in message
news:57D4D054-2A0D-4E4F-A09E-E1AAA1C32B5B@.microsoft.com...
> Hi Dan,
> I got this error message in MSSQL 2000 not SQL 7.0.
> Thanks,
> Joanne
>|||Thanks a lot Dan
Jodie