Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-15
2
Medium Priority
?
295 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 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

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 This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

718 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