dml without generating log transactions ?

Hi There

I know the answer to this is probably no, but had to ask anyway.

Is there a way to perform a dml statement without generating anything in the transaction log ?

The reason i ask is that i have a database that uses simply recovery model, however i need to move a 1 billion row table to this DB, i know that even though it is in simple recovery it is one transaction, it will be written to the log until committed then the space will be released in the log file.

I am using a simple: insert into DW_DB..table select * from DB..other_table.

I have dropped all indexes before the operation.

However this is a big problem, the log for the db in simple recovery that i am moving the data to grew to 128 Gig and the disk ran out of space, the other drives on the machine do not have much space.

Is there a way i can move the billion row table into the new DB without generating such a huge log ?

Thanx

Hi There

Part 2 for the question:

The transaction has rolled back, however there is still 27 gigs space used in the transaction log, there are no open transactions in the db, the db is in simple recovery, i cannot backup the log as it is simple recovery, what is this 27 gigs in the transaction and how do i clear it ?

Thanx

|||

Please ignore my second comment, this problem went away after checkpointing the database, however any feedback ont he original post would be greatly appreciated.

Thanx

|||You can use select into command which is bulk operation and it is minimally logged in the case of simple recovery model.|||Thank you , this worked perfectly.

Distribution agent failed but didn't ?

Hi There

I have the following setup.

Server A transactionally replicates to Server B , using Server C as remote distributor.

Now the distribution agent runs on Server B, under distribution agents in replication monitor the distribution agent is the same job as the job on Server B defined as REPL-Dsitributor, correct ?

Now this morning at 4am under distribution agents on the remote distributor the job was marked as failed (error: no response in 10 minutes), but the "same job" on Server B, was not failed ?

The replication agent failure alert did not fire, neither did the notification failure alert on the job it self ?

What the %#$&#% happened ? This is a production environment so it is very worrying ?

Only according to the distribution agent on the remote distributor did the job fail, but not the job itself on the subscriber ?

Firstly what happened here ?
Secondly how can i make the agent keep retrying ? Once it failed it stayed failed, can i configure it to keep trying ?

Thanx
How can i make this agent keep retrying ?As for your case, distribution agent may not run on B but C (remote distributor) and replmonitor actually monitor agent job on C.

If your subscription is push, distribution agent will run under C. But if your subscription is pull, it will run under B.

About your another question, if agent job can keep retrying if it fails. That's depends, if it fails to access some meta data or subscription database, some access issue, it will retry five times and fail. Other problem cause the agent job fail, it just fail, until your solve the problem and restart it again. Otherwise, it won't run.

Hope it will help you some.

-Yunjing

Distributed Transactions ?

Hi There

I realize this is probably a basic question or at least i hope so but i cannot get distributed transactions working.

BOL is just driving me in circles.
I am running SS2000, MSDTC is up and running on the server.

I have added a trigger to a table where on insert or update the triggers fires and inserts a row to a remote server.

When i execute the update or insert i get the error:
Cannot start a distributed transaction.

I have tried stating BEGIN DISTRIBUTED TRANSACTION before the update but i get the same error, i have also read up on SET REMOTE_PROC_TRANSACTIONS, but this is not a remote sp.

Can anyone please reccomend a good link as to how to get distributed transactions to work.

Thanx
P.S I have already checked this link http://support.microsoft.com/?kbid=816701.

Also i have found references to checking avrious services are running etc, seems there is alot to setup to get MSDTC to work but i cannot find the setup in BOL, not sure everything i have to do to egt MSDTC to work ?

Please help Thanx
|||

Have you tried setting XACT_ABORT ton ON?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Hi

Tried this

SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
UPDATE PSAPMSGSUBCON SET STATUSSTRING = 'ERROR'

Got this error:

[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Msg 7391, Level 16, State 1, Procedure PSAPMSGSUBCON_MESSAGE_ERROR_TRIGGER, Line 11
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

ALso found this in BOL:

It is required that XACT_ABORT be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions. For more information, see Distributed Queries and Distributed Transactions.

I think i am simply missing something to ensure MSDTC is setup and running correctly.
Is the only thing require for MSDTC to work , that it be running, servers have the network DTC enabled and you stipulate

BEGIN DISTRIBUTED TRANSACTION

?