Non-logged insert... select

Hi,

Is there any way in either sql2k or 2005 to do an "insert ... select" without writing to the tran log, similar to a non-logged bcp from a flat file? It seems crazy that I should have to go outside the engine and back in again just to avoid this logging. Or am I mistaken, and this is already what happens when you do an insert... select when the db is in bulk logged or simple mode?

Thanks!

Mike

Hey Mike. You can make use of the 'select...into' statement to perform this type of operation in bulk, of course that means the target table can't already exist. There is no way to perform an 'insert' statement with minimal logging. The only options you have for performing minimal logged operations in SQL 2000 are the following commands:

- BULK INSERT
- bcp
- select...into
- create index
- text and image operations (updatetext and writetext)

There are also prerequisites for minimally logged operations, including:

- Table not being replicated
- Table level locking is specified/used
- Specific index considerations (different depending on types of indexes present, whether or not data exists in the table, etc...see BOL for more information)

In SQL 2005, you have the option of using any of the above, and also the new 'BULK' option with the OPENROWSET command to load data from a text file using minimal logging. I suspect this may be your best option if you are running SQL 2005 and loading data into an existing table. The prerequisites for bulk loading still apply though. For more information on this new 2005 bulk operation, see BOL.

Also in SQL 2005, you could use one of the bulk load commands to bulk load data into a new table using minimal logging, then switch that table's data into an existing table if you make use of table partitioning correctly...the switch into the existing table's partition scheme could be a meta-data only operation if you design the partitioning strategy correctly. See SQL 2005 BOL for more information on table partitioning, or just post another question here and we'll see what we can get you for pointers.

HTH,

|||

Can I use the select...into and try to partition the table?

Such as:

select top(1000)*

into dbo.create_on_the_fly_tbl

from dbo.any_existing_tbl

on partitionrangeSCM(id)

Non-logged insert... select

Hi,

Is there any way in either sql2k or 2005 to do an "insert ... select" without writing to the tran log, similar to a non-logged bcp from a flat file? It seems crazy that I should have to go outside the engine and back in again just to avoid this logging. Or am I mistaken, and this is already what happens when you do an insert... select when the db is in bulk logged or simple mode?

Thanks!

Mike

Hey Mike. You can make use of the 'select...into' statement to perform this type of operation in bulk, of course that means the target table can't already exist. There is no way to perform an 'insert' statement with minimal logging. The only options you have for performing minimal logged operations in SQL 2000 are the following commands:

- BULK INSERT
- bcp
- select...into
- create index
- text and image operations (updatetext and writetext)

There are also prerequisites for minimally logged operations, including:

- Table not being replicated
- Table level locking is specified/used
- Specific index considerations (different depending on types of indexes present, whether or not data exists in the table, etc...see BOL for more information)

In SQL 2005, you have the option of using any of the above, and also the new 'BULK' option with the OPENROWSET command to load data from a text file using minimal logging. I suspect this may be your best option if you are running SQL 2005 and loading data into an existing table. The prerequisites for bulk loading still apply though. For more information on this new 2005 bulk operation, see BOL.

Also in SQL 2005, you could use one of the bulk load commands to bulk load data into a new table using minimal logging, then switch that table's data into an existing table if you make use of table partitioning correctly...the switch into the existing table's partition scheme could be a meta-data only operation if you design the partitioning strategy correctly. See SQL 2005 BOL for more information on table partitioning, or just post another question here and we'll see what we can get you for pointers.

HTH,

|||

Can I use the select...into and try to partition the table?

Such as:

select top(1000)*

into dbo.create_on_the_fly_tbl

from dbo.any_existing_tbl

on partitionrangeSCM(id)

NonLogged Bulk copy syntax

SQL 7.0
I need to use nonlogged bcp to copy from a table with 90
millin records to an empty table structure that's
different than the source structure.
Does someone know the syntax?
old table example:
name, char, 30
address,char,30
new table example:
name,char,30
id,decimal,10 this is NULL on the source file structure
address, char,30
thanks,
DonUse select into statement
That is not logged.
You can select * or select fieldlist.
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||Just to be clear you can never have a non-logged operation in SQL Server.
You can get what is called a Minimally Logged operation by doing a Select
Into, BCP or Bulk Insert when the proper conditions are set. This still
logs entries to the transaction log but are minimal compared to the fully
logged operations. In the case of the Select Into you need to be in the
Bulk Logged or Simple recovery mode for this to occur.
Andrew J. Kelly SQL MVP
<news.optonline.net> wrote in message
news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
> Use select into statement
> That is not logged.
> You can select * or select fieldlist.
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>|||To do this I would create a view on the new table
create view vw_newtable
as
select * from newtable
than bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -N'
Note there must be no indexes on the dest table.
For 90 million recs I would consider doing the above but maybe 10 million
recs at a time. Use a query to extract the data with queryout on an indexed
column (or you can use views in the same way)
"Don" wrote:

> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||I'm using SQL 7.0 and the modes you mention are for SQL
2000.
Don

>--Original Message--
>Just to be clear you can never have a non-logged
operation in SQL Server.
>You can get what is called a Minimally Logged operation
by doing a Select
>Into, BCP or Bulk Insert when the proper conditions are
set. This still
>logs entries to the transaction log but are minimal
compared to the fully
>logged operations. In the case of the Select Into you
need to be in the
>Bulk Logged or Simple recovery mode for this to occur.
>
>--
>Andrew J. Kelly SQL MVP
>
><news.optonline.net> wrote in message
>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
message[vbcol=seagreen]
90[vbcol=seagreen]
structure[vbcol=seagreen]
>
>.
>|||I guess I don't follow. for one thing you say bcp in, but
then the command is out.
then i don't see where anything done in the view is used
again in the bcp commands.
thanks,
don

>--Original Message--
>To do this I would create a view on the new table
>create view vw_newtable
>as
>select * from newtable
>than bcp the data out
>master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
N'
>then bcp in
>master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
N'
>Note there must be no indexes on the dest table.
>For 90 million recs I would consider doing the above but
maybe 10 million
>recs at a time. Use a query to extract the data with
queryout on an indexed
>column (or you can use views in the same way)
>
>
>
>"Don" wrote:
>
90[vbcol=seagreen]
>.
>|||Then make sure you set the Select Into / Bulk Insert mode and it will be the
same.
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:07db01c4c9bb$f22588f0$a501280a@.phx.gbl...[vbcol=seagreen]
> I'm using SQL 7.0 and the modes you mention are for SQL
> 2000.
> Don
>
> operation in SQL Server.
> by doing a Select
> set. This still
> compared to the fully
> need to be in the
> message
> 90
> structure|||doh (deliberate mistake - left to the reader)
create view vw_newtable
as
select * from newtable
then bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..vw_newtbl in c:\newtbl.txt -N'
"Don" wrote:

> I guess I don't follow. for one thing you say bcp in, but
> then the command is out.
> then i don't see where anything done in the view is used
> again in the bcp commands.
> thanks,
> don
>
> N'
> N'
> maybe 10 million
> queryout on an indexed
> 90
>

NonLogged Bulk copy syntax

SQL 7.0
I need to use nonlogged bcp to copy from a table with 90
millin records to an empty table structure that's
different than the source structure.
Does someone know the syntax?
old table example:
name, char, 30
address,char,30
new table example:
name,char,30
id,decimal,10 this is NULL on the source file structure
address, char,30
thanks,
DonUse select into statement
That is not logged.
You can select * or select fieldlist.
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||Just to be clear you can never have a non-logged operation in SQL Server.
You can get what is called a Minimally Logged operation by doing a Select
Into, BCP or Bulk Insert when the proper conditions are set. This still
logs entries to the transaction log but are minimal compared to the fully
logged operations. In the case of the Select Into you need to be in the
Bulk Logged or Simple recovery mode for this to occur.
Andrew J. Kelly SQL MVP
<news.optonline.net> wrote in message
news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
> Use select into statement
> That is not logged.
> You can select * or select fieldlist.
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with 90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file structure
>> address, char,30
>> thanks,
>> Don
>|||To do this I would create a view on the new table
create view vw_newtable
as
select * from newtable
than bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -N'
Note there must be no indexes on the dest table.
For 90 million recs I would consider doing the above but maybe 10 million
recs at a time. Use a query to extract the data with queryout on an indexed
column (or you can use views in the same way)
"Don" wrote:
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||I'm using SQL 7.0 and the modes you mention are for SQL
2000.
Don
>--Original Message--
>Just to be clear you can never have a non-logged
operation in SQL Server.
>You can get what is called a Minimally Logged operation
by doing a Select
>Into, BCP or Bulk Insert when the proper conditions are
set. This still
>logs entries to the transaction log but are minimal
compared to the fully
>logged operations. In the case of the Select Into you
need to be in the
>Bulk Logged or Simple recovery mode for this to occur.
>
>--
>Andrew J. Kelly SQL MVP
>
><news.optonline.net> wrote in message
>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
>> Use select into statement
>> That is not logged.
>> You can select * or select fieldlist.
>>
>> "Don" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file
structure
>> address, char,30
>> thanks,
>> Don
>>
>
>.
>|||I guess I don't follow. for one thing you say bcp in, but
then the command is out.
then i don't see where anything done in the view is used
again in the bcp commands.
thanks,
don
>--Original Message--
>To do this I would create a view on the new table
>create view vw_newtable
>as
>select * from newtable
>than bcp the data out
>master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
N'
>then bcp in
>master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
N'
>Note there must be no indexes on the dest table.
>For 90 million recs I would consider doing the above but
maybe 10 million
>recs at a time. Use a query to extract the data with
queryout on an indexed
>column (or you can use views in the same way)
>
>
>
>"Don" wrote:
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file structure
>> address, char,30
>> thanks,
>> Don
>>
>.
>|||Then make sure you set the Select Into / Bulk Insert mode and it will be the
same.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:07db01c4c9bb$f22588f0$a501280a@.phx.gbl...
> I'm using SQL 7.0 and the modes you mention are for SQL
> 2000.
> Don
>>--Original Message--
>>Just to be clear you can never have a non-logged
> operation in SQL Server.
>>You can get what is called a Minimally Logged operation
> by doing a Select
>>Into, BCP or Bulk Insert when the proper conditions are
> set. This still
>>logs entries to the transaction log but are minimal
> compared to the fully
>>logged operations. In the case of the Select Into you
> need to be in the
>>Bulk Logged or Simple recovery mode for this to occur.
>>
>>--
>>Andrew J. Kelly SQL MVP
>>
>><news.optonline.net> wrote in message
>>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
>> Use select into statement
>> That is not logged.
>> You can select * or select fieldlist.
>>
>> "Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
> 90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file
> structure
>> address, char,30
>> thanks,
>> Don
>>
>>
>>.|||doh (deliberate mistake - left to the reader)
create view vw_newtable
as
select * from newtable
then bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..vw_newtbl in c:\newtbl.txt -N'
"Don" wrote:
> I guess I don't follow. for one thing you say bcp in, but
> then the command is out.
> then i don't see where anything done in the view is used
> again in the bcp commands.
> thanks,
> don
> >--Original Message--
> >To do this I would create a view on the new table
> >create view vw_newtable
> >as
> >select * from newtable
> >
> >than bcp the data out
> >
> >master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
> N'
> >then bcp in
> >master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
> N'
> >
> >Note there must be no indexes on the dest table.
> >For 90 million recs I would consider doing the above but
> maybe 10 million
> >recs at a time. Use a query to extract the data with
> queryout on an indexed
> >column (or you can use views in the same way)
> >
> >
> >
> >
> >
> >
> >"Don" wrote:
> >
> >> SQL 7.0
> >>
> >> I need to use nonlogged bcp to copy from a table with
> 90
> >> millin records to an empty table structure that's
> >> different than the source structure.
> >> Does someone know the syntax?
> >>
> >> old table example:
> >> name, char, 30
> >> address,char,30
> >>
> >> new table example:
> >> name,char,30
> >> id,decimal,10 this is NULL on the source file structure
> >> address, char,30
> >>
> >> thanks,
> >> Don
> >>
> >>
> >.
> >
>sql

NonLogged Bulk copy syntax

SQL 7.0
I need to use nonlogged bcp to copy from a table with 90
millin records to an empty table structure that's
different than the source structure.
Does someone know the syntax?
old table example:
name, char, 30
address,char,30
new table example:
name,char,30
id,decimal,10 this is NULL on the source file structure
address, char,30
thanks,
Don
Use select into statement
That is not logged.
You can select * or select fieldlist.
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>
|||Just to be clear you can never have a non-logged operation in SQL Server.
You can get what is called a Minimally Logged operation by doing a Select
Into, BCP or Bulk Insert when the proper conditions are set. This still
logs entries to the transaction log but are minimal compared to the fully
logged operations. In the case of the Select Into you need to be in the
Bulk Logged or Simple recovery mode for this to occur.
Andrew J. Kelly SQL MVP
<news.optonline.net> wrote in message
news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
> Use select into statement
> That is not logged.
> You can select * or select fieldlist.
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>
|||To do this I would create a view on the new table
create view vw_newtable
as
select * from newtable
than bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -N'
Note there must be no indexes on the dest table.
For 90 million recs I would consider doing the above but maybe 10 million
recs at a time. Use a query to extract the data with queryout on an indexed
column (or you can use views in the same way)
"Don" wrote:

> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>
|||I'm using SQL 7.0 and the modes you mention are for SQL
2000.
Don

>--Original Message--
>Just to be clear you can never have a non-logged
operation in SQL Server.
>You can get what is called a Minimally Logged operation
by doing a Select
>Into, BCP or Bulk Insert when the proper conditions are
set. This still
>logs entries to the transaction log but are minimal
compared to the fully
>logged operations. In the case of the Select Into you
need to be in the[vbcol=seagreen]
>Bulk Logged or Simple recovery mode for this to occur.
>
>--
>Andrew J. Kelly SQL MVP
>
><news.optonline.net> wrote in message
>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
message[vbcol=seagreen]
90[vbcol=seagreen]
structure
>
>.
>
|||I guess I don't follow. for one thing you say bcp in, but
then the command is out.
then i don't see where anything done in the view is used
again in the bcp commands.
thanks,
don

>--Original Message--
>To do this I would create a view on the new table
>create view vw_newtable
>as
>select * from newtable
>than bcp the data out
>master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
N'
>then bcp in
>master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
N'
>Note there must be no indexes on the dest table.
>For 90 million recs I would consider doing the above but
maybe 10 million
>recs at a time. Use a query to extract the data with
queryout on an indexed[vbcol=seagreen]
>column (or you can use views in the same way)
>
>
>
>"Don" wrote:
90
>.
>
|||Then make sure you set the Select Into / Bulk Insert mode and it will be the
same.
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:07db01c4c9bb$f22588f0$a501280a@.phx.gbl...[vbcol=seagreen]
> I'm using SQL 7.0 and the modes you mention are for SQL
> 2000.
> Don
> operation in SQL Server.
> by doing a Select
> set. This still
> compared to the fully
> need to be in the
> message
> 90
> structure
|||doh (deliberate mistake - left to the reader)
create view vw_newtable
as
select * from newtable
then bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..vw_newtbl in c:\newtbl.txt -N'
"Don" wrote:

> I guess I don't follow. for one thing you say bcp in, but
> then the command is out.
> then i don't see where anything done in the view is used
> again in the bcp commands.
> thanks,
> don
> N'
> N'
> maybe 10 million
> queryout on an indexed
> 90
>

non logged transactions with transactional replication

I know that if you're doing log shipping and someone runs a fast bcp ( non
logged ) activity against the database that subsequent transaction log dumps
won't load until you do another full dump and load. This can be a problem,
because of the time involved in dumping and loading. How does transactional
replication handle non logged activity? The same way as log shipping? Which
would be bad, or does the data that was inserted or changed as a result of
the non logged transaction make it to the target server?
John,
it is my understanding that in the full recovery model, every change to the
database is logged, so there's no issue with transactional replication. In
SQL Server 2005 the transaction log may be backed up after minimally logged
operations (eg Bulk load operations in the bulk-logged model) so log
shipping is also possible.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||But, if you run a transaction that is truly minimally logged (BCP in
bulk-logged recovery model), you will have to reinitialize.
I don't understand why that is considered "bad". A minimally logged
transaction does not write everything to the tran log. Therefore, if you
were allowed to backup the tran log and restore it, you would wind up with a
corrupted database. With respect to replication, a minimally logged
transaction doesn't even hit the replication engine, so your publisher and
subscriber will completely out of synch and since there is no track of the
data, there is no ability to resynch them except by reinitializing.
(Replication doesn't even detect that a minimally logged transaction
occured.)
This is not an issue for either replication or log shipping. It is a side
effect of the way the transaction was executed. The replication and log
shipping features are simply protecting the integrity of the databases
against something they can not control.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OMFC2BDLGHA.648@.TK2MSFTNGP14.phx.gbl...
> John,
> it is my understanding that in the full recovery model, every change to
> the database is logged, so there's no issue with transactional
> replication. In SQL Server 2005 the transaction log may be backed up after
> minimally logged operations (eg Bulk load operations in the bulk-logged
> model) so log shipping is also possible.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It doesn't handle not logged activity because it is based on logged events
and you can't do non-logged events on tables you are replicating.
Non-logged activity will occur when the bulk copy recovery model is selected
and
1) you do certain operations, i.e. create index, select into, etc
2) fast bcp - which requires no indexes on the table among other things
transactional replication requires a table with an index, so you can't do
fast bcp with it. It also requires publications built on preexisting tables
so you won't be able to replicate a table you are selecting into.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <John@.discussions.microsoft.com> wrote in message
news:59BAC0A1-DE3B-4E1B-A121-0C4E9B78497C@.microsoft.com...
>I know that if you're doing log shipping and someone runs a fast bcp ( non
> logged ) activity against the database that subsequent transaction log
> dumps
> won't load until you do another full dump and load. This can be a problem,
> because of the time involved in dumping and loading. How does
> transactional
> replication handle non logged activity? The same way as log shipping?
> Which
> would be bad, or does the data that was inserted or changed as a result of
> the non logged transaction make it to the target server?
|||Yes, you can in fact perform minimally logged operations on tables that are
being replicated. Writetext and updatetext come to mind.
Also there is no such thing as a "non-logged" event. Every single operation
that manipulates data will place some kind of entry into the log.
Therefore, the correct term is "minimally logged" and it has been for about
a decade now.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eLcsW4vLGHA.1192@.TK2MSFTNGP11.phx.gbl...
> It doesn't handle not logged activity because it is based on logged
> events and you can't do non-logged events on tables you are replicating.
> Non-logged activity will occur when the bulk copy recovery model is
> selected and
> 1) you do certain operations, i.e. create index, select into, etc
> 2) fast bcp - which requires no indexes on the table among other things
> transactional replication requires a table with an index, so you can't do
> fast bcp with it. It also requires publications built on preexisting
> tables so you won't be able to replicate a table you are selecting into.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "John" <John@.discussions.microsoft.com> wrote in message
> news:59BAC0A1-DE3B-4E1B-A121-0C4E9B78497C@.microsoft.com...
>