Post reply

BULK Insert zwischen Datenbanken

  • October 5, 2012 at 4:01 am

    #393322

    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?

  • October 5, 2012 at 5:38 am

    #1545999

    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.

  • October 5, 2012 at 5:59 am

    #1546008

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 8, 2012 at 10:48 am

    #1546844

    SQL Server muss immer the log gescrieben.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • October 9, 2012 at 2:26 pm

    #1547374

    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.

  • October 12, 2012 at 7:53 am

    #1548650

    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.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply