BULK Insert zwischen Datenbanken

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » BULK Insert zwischen Datenbanken

BULK Insert zwischen Datenbanken

Rate Topic

Display Mode

Topic Options

Author

Message

RKA-202364

Posted Friday, October 05, 2012 4:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, October 08, 2012 4:44 AM
Points: 1, Visits: 10

Hallo,

ich lade mir einen Katalog (ca. 800.000 Datensätze) aus einer anderen, entfernten DB (über Verbindungsserver).
Das Wiederherstellungsmodell steht auf FULL. Ist es richtig, dass für alle 800K Sätze ein Log geschrieben wird? Wenn ja,
wie kann ich das verhindern?

Ich könnte das Model während des Imports auf BULK_LOGGED umschalten, aber das wirkt sich ja
wieder nur bei BULK INSERT (der hier nicht anwendbar ist) aus, oder?

translate per google:
I upload a catalog (about 800,000 records) from another, remote DB (via linked server).
The recovery model is set to FULL. Is it true that for every 800K records a log is written? If so,
how can I prevent it?

I could switch the model during import to BULK_LOGGED, but that affects so
again only for BULK INSERT (the not applicable here) is from, right?

Post #1368921

Phil Parkin

Posted Friday, October 05, 2012 5:38 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 4,247, Visits: 9,500

Is there a chance that you could post this in English?

Most people here communicate in English and I think you will find that you get a better response if you try.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1368950

Lowell

Posted Friday, October 05, 2012 5:59 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 11,648, Visits: 27,768

Google Translate (German to English) (10/5/2012)

Hello,

I upload a catalog (about 800,000 records) from another, remote DB (via linked server).
The recovery model is set to FULL. Is it true that for every 800K records a log is written? If so,
how can I prevent it?

I could switch the model during import to BULK_LOGGED, but that affects so
again only for BULK INSERT (the not applicable here) is from, right?

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1368959

ScottPletcher

Posted Monday, October 08, 2012 10:48 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:56 PM
Points: 1,324, Visits: 1,778

SQL Server muss immer the log gescrieben.

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1369952

RonKyle

Posted Tuesday, October 09, 2012 2:26 PM

Say Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 9:32 AM
Points: 671, Visits: 1,509

SQL Server muss immer the log gescrieben.

Ein Log muss immer geschrieben werden.

I'm not validating the answer, just putting it in the correct syntax. Computer translations only go so far.

Post #1370582

EdVassie

Posted Friday, October 12, 2012 7:53 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:26 AM
Points: 2,621, Visits: 2,759

SQL Server will always write to the log. This is needed because if the INSERT command fails, SQL Server needs to put the database back too the state it was in before the INSERT ran.

If you do a Bulk Insert, you can reduce the amount of log written by putting the database recover mode to Bulk Insert. In this situation, SQL Server will only log changes to Space Map and other essential pages, so that if the bulk insert fails it can put these back to what they were before you started your work.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.

Post #1372140

« Prev Topic | Next Topic »

Permissions