Solved

What does SQL server do when bulk loading data into a Database in Simple mode.

Posted on 2011-03-15
2
292 Views
Last Modified: 2012-05-11
Can some one tell me what SQL server does when bulk loading data into a database that is in simple mode.

Such as file growth, table space growth etc. what about transactions how are they handled?
0
Comment
Question by:Steve Samson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 35148549
In SIMPLE recovery mode, only enough data is kept in the logs to properly roll back uncommitted transactions or roll forward transactions that committed but haven't been written to the data files yet - it's minimal.

During the bulk load, your table grows as a normal rate, but the log space used is minimal, as it's only enough to hold the currently uncommitted (or committed but unflushed) rows.

When you're bulk-loading data and your database is set to SIMPLE recovery mode, it's not itself wrapped in a transaction, so a failed bulk-load could leave half the data in your database. As I understand it, setting your recovery mode to BULK-LOAD will basically put it in SIMPLE mode, with the added benefit the bulk operations can also be rolled forward and backward as an atomic unit.
0
 

Author Closing Comment

by:Steve Samson
ID: 35149001
consise
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 108
Sql Query join multiple table and distinct records 7 31
SQL Server - Set Field Values ito Zero Based on Related Table 4 44
What is this datetime? 1 26
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question