.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

BULK INSERT under the full recovery model

Posted By:      Posted Date: October 17, 2010    Points: 0   Category :Sql Server

Hi Experts, is there any performance difference between BULK INSERT and row-insert operations under full recovery model? My database is configured as full recovery model, so I want to konw do I still try use BULK operations to modify data? Thanks!

View Complete Post

More Related Resource Links

Bulk and full recovery model


hi team,


i have one dought,


in Full Recovery model have bulk log opertaions restore.

bulk log Recovery model also have bulk log operaions and bulk insert operations 


what are the difference in these two bulks could any body please explaine me.





TLog backup size in Full vs. Bulk logged recovery model.

When executing some minimally logged operations one can notice higher in TLog growth in Full recovery vs that of Bulk. But when it comes to talking a TLog backup one can notice its bigger (because the modified extents are also backed up along with TLog) Questions: 1. Are the pages backedup only those which were modified by minimally logged operation? 2. TLog backups in bulk recovery will be bigger than those from Full recovery but it depends if or not any minimally logged operations were done.

Simple Recovery Model Yet Transaction Log Is Full

Hi there, There's a database which has been configured with SIMPLE recovery model but yet the transaction log will grow and fill up the disk at times. Is there anything one can do to prevent the log from filling up the disk? Thanks in advance.

how to understand full recovery model can restore to the point of failure


if I backup log hourly, 50 minutes after last log backup, sql server corrupts, I guess I can only restore to the last tlog backup, how to understand restore to the point of failure?

also if I use simple recovery model, backup differentially every hour, do I have the same data protection? I know answer is no, but how to understand it?



Question on Bulk-Logged Recovery Model


After reading this MSDN article , I'm a bit baffled about how bulk operations are logged...

If data extents are used for log backup, what data will be logged? Won't it be the data at a stage after several committed transactions have already been performed on those extents?

Just for my better understanding of the concept: Isn't it necessary to log the extents containing data at the time the bulk operation occured?

Is this the reason why restore operations cannot be performed on a point-in-time basis?

Thanks for helping me understand...

Is a database online when it is changed from full to simple recovery model?



We have some databases that occaisally have very large ldf files and I have learned that the best way to shrink the ldf file is to change the backup mode to simple, shrink the log file, convert it back to full and do a full database backup so that the log file backups will run again. My question is during this process is the database available to the end users? When you guys do this do you feel it is nessesary to let the end users know or will they not notice what is going on?



Thanks! Kevin


Can I bulk insert to specific columns when bulk inserting a text file or do I have to bulk insert into all columns of a table? The extra column in the table is an identity integer. So I had to create a view without the identity integer to make it work. Maybe there is a more direct way?? If so what would be the syntax? I am a beginner to intermediate SQL guy.   strSQL = "BULK " & _        "INSERT CustomersUpload " & _        " FROM 'C:\MydataFile.txt' " & _        " WITH " & _        " ( " & _        " FIELDTERMINATOR = '|', " & _        " ROWTERMINATOR = '\n' " & _        " )" Thanks

Errors during bulk insert

Hi, While writing a stored procedure in SQL 2008, for a bulk insert, I am getting several errors. Part of the SP is written below for your reference. Need to know where am I going wrong. Any help will be thankfully acknowledged.-Learn24x7----------CREATE PROCEDURE Sp_BulkInsert(@Directory nvarchar(256), @TblName nvarchar(50), @Collation nvarchar(50), @FieldSeparator nvarchar(50),    @RowTerminator nvarchar(50))ASBEGIN    SET NOCOUNT ON;                BULK INSERT [@TblName]    FROM ''+@Directory+'' --Error1: see below    WITH --Error2: see below     (     FIRSTROW = 2,     CODEPAGE = (SELECT CAST(@Collation AS INT)), --Error3: see below     FIELDTERMINATOR = ''+@FieldSeparator+'',     ROWTERMINATOR = ''+@RowTerminator+''     )  ENDGO----------FROM ''+@Directory+'' --Error1: An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.WITH --Error2: Incorrect syntax near the keyword 'with'. If this statement is a commo

Unable to prepare the SSIS bulk insert for data insertion

Hi,   I am using SQL Server Destinations in my data flow tasks. I'm running this package in the server until i encountered this error:   OnError,,,LOAD AND UPDATE Dimension Tables,,,10/24/2007 1:22:23 PM,10/24/2007 1:22:23 PM,-1071636367,0x,Unable to prepare the SSIS bulk insert for data insertion. OnError,,,Load Dimensions,,,10/24/2007 1:22:23 PM,10/24/2007 1:22:23 PM,-1071636367,0x,Unable to prepare the SSIS bulk insert for data insertion. OnError,,,Discount Reason, ISIS Condition, ISIS Defect, ISIS Repair, ISIS Section, ISIS Symptom, Job Status, Parts, Purchase SubOrder Type, Service Contract, Service Reason, Service Type, TechServiceGrp, WarrantyType, Branch, Wastage Reason,,,10/24/2007 1:22:23 PM,10/24/2007 1:22:23 PM,-1073450974,0x,SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Dim_T_ISISDefect" (56280) failed with error code 0xC0202071. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure. What could be the reason for this? I don't usually have an error.   cherriesh

SQL ServerDestination error - Unable to prepare the SSIS bulk insert for data insertion.

Having searched the forum, this one clearly has form... However beyond assisting those who have fallen at the first hurdle (i.e. forgetting/not knowing that they cannot execute the package remotely to the instance of SQL Server into which they are inserting), the issues raised by others have not been addressed. Thus I am bringing nothing new to the table here - just providing an executive summary of problems which others have run into, written about, but not received answers for.   First the complete error: Description: Unable to prepare the SSIS bulk insert for data insertion.  End Error  Error: 2008-01-15 04:55:27.58     Code: 0xC004701A     Source: <xxx> DTS.Pipeline     Description: component "<xxx> failed the pre-execute phase and returned error code 0xC0202071.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  4:53:34 AM  Finished: 5:00:00 AM  Elapsed:  385.384 seconds.  The package execution failed.  The step failed.   Important points It mostly works - It produces no error more than 9 times out of 10. It fails on random dataflows - My package has several dataflows, (mostly) executing concurrently. Where the error occurs it does not do so on the same dataflow each time: on one run it'll fail on dataflow A whilst B,C,D and E succeed, then A-E will all succeed (and continue doing so for the next ten runs thereafter), and

Get error message from bulk insert task.

Hello, I've created a dtsx package that bulk inserts some data from a csv file into a sql table (using the BULK INSERT TASK). Because this package is scheduled to run automatically, I also added a EXECUTE SQL TASK that will insert a row in a 'log table'. It is linked to the BULK INSERT task with a 'Succes constraint (green arrow)', so I know about all the successes. But I am more interested in the failures, so I also added another EXECUTE SQL TASK and linked it to the BULK INSERT task with the 'failure constraint (red arrow)'. Question: How can I get the error message from the BULK INSERT TASK (like 'file does not exist' or 'error converting .....') and store it in a field in the 'log table'? Thanks in advance, Peter

How to do a parameterized bulk insert into SQL Server 2008 R2 from C# (.NET 4.0)

Hi, I'm trying to execute a BULK INSERT with a parameterized SqlCommand from a C# .NET 4.0 project. This is the code: private System.Data.SqlClient.SqlCommand sqlCmd2; private string sqlCmd2String = "BULK INSERT ExchangeTrackingLogs.dbo.ParsedLogs FROM @name WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n', FIRSTROW=2)"; private System.Data.SqlClient.SqlParameter[] sqlCmd2Params = new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.VarChar) }; (...) sqlCmd2Params[0].Value = source; sqlCmd2.Parameters.Clear(); sqlCmd2.Parameters.AddRange(sqlCmd2Params); sqlCmd2.ExecuteNonQuery(); where source is a string containing the full path to a file, i.e. a value "C:\\TEMP\\MSGTRK20100701-1.LOG_EXHUB01". When I execute this code, I get an Exception with Message "Incorrect syntax near '@name'." In the database trace I see an RPC:Completed event with TextData equal to exec sp_executesql N'BULK INSERT ExchangeTrackingLogs.dbo.ParsedLogs FROM @name WITH (FIELDTERMINATOR='','', ROWTERMINATOR='' '', FIRSTROW=2)',N'@name varchar(36)',@name='C:\TEMP\MSGTRK20100701-1.LOG_EXHUB01' I tried using verbatim string literals, but doesn't seem to help. Any idea?

Bulk Insert Problem

I am trying to perform a bulk insert on pipe delimited file (approx 25 columns,5000+ rows of data).  Normally this is no problem however this file has a header and trailer record with no delimiters (1 column).  I could use a utility like grep to remove the header/trailer record but I would like to capture the entire file inside sql server. I can bulk insert the entire file into a single column (wide) that would include the delimiters.  My problem from there is to process the single column data into the appropriate rows /columns.  I am looking for a set solution as opposed to a cursor type solution. HTML?  Seems slow.  Cross Apply?   Changing the file format is not a viable solution. I’m likely to have more than a few of these oddball formats with a variable number of columns.  Any ideas?   I thank you for your time on this puzzle.

Bulk insert CSV files

Hello, I am trying to Bulk Insert a CSV file, and I can't seem to find the row terminator. When I use \m I get an error message that an unexpected EOF has occurred, which usually means a carriage return at the end of the file. I removed the carriage return from the file, but I am still getting the same error message. I have tried the following row delimiters... \r,\n,{CR},{CR}{LF}. These work as the data is indeed bulk loaded into the table, however it dumps the majority of the data in the last column, so 7 or 8 columns are stuffed into 1. Has anyone had this issue before? Any help would be appreciated. DaveDave SQL Developer

SharePoint 2010 Content DB Recovery Model

Hello Everyone, the following link states that the content db for SharePoint 2010 uses simple recovery model. http://technet.microsoft.com/en-us/library/dd207314.aspx   whereas this link at SQL Server documentation says that Simple model is not suitable for production systems because it has risk of data loss http://msdn.microsoft.com/en-us/library/ms189275.aspx   These two look totally contradictory to me.  

Permission Denied to SA on Bulk insert

Dear All,I am experiencing strange problem (SQL 2005, build 9.0.1399), receiving following error:Msg 4834, Level 16, State 4, Line 1You do not have permission to use the bulk load statement.I am running the stored procedure SP1.  This stored procedure calls another one, SP2.  Stored procedure SP2 contains a dynamic SQL statement to insert data into the temporary table, created by SP1 and given to SP2 via parameter name.Something like:SET @szSQL = 'BULK INSERT ' + @TName + ' FROM ' + '''' + @FilePath + '''' +  ' WITH ( ' +   ' DATAFILETYPE = ''char'', ' +      ' FIELDTERMINATOR = ' + '''' + @Terminator + ''', ' +      ' ROWTERMINATOR = ''\n'' ' + ' ) 'EXEC (@SQL)If I execute SP1 I receive an errorMsg 4834, Level 16, State 4, Line 1You do not have permission to use the bulk load statement.But I can execute SP2 with no problem.All operations are done under SA login.Any thoughts/ideas?Thanks

BULK INSERT with a UNC path does not work even with delegation

I have a stored procedure that uses BULK INSERT with a UNC path to another server. I have enabled both the server and the domain account the SQL Server services are running under to allow all delegation. The UNC path allows Everyone Full Control. I can log into my computer using the SQL Server service account and browse to the file and open it. I can log onto the SQL Server with the service account or my account and browse to the file and open it. Whenever I run the stored procedure or execute the BULK INSERT from SSMS I get the following: Msg 4861, Level 16, State 1, Line 2 Cannot bulk load because the file "\\nas\shared\ZipSales\FLAT.txt" could not be opened. Operating system error code 5(Access is denied.). I can log into the server console using my user account, open SSMS on the server, and the stored procedure or the raw BULK INSERT work just fine. It's only a problem when I execute it from an end user workstation. Moving the data file to the SQL server works fine, but that is not a viable long-term solution. After scouring the Internet for most of two days, I finally stumbled across <a href="http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/512fe230-b94a-4fe0-83c8-201a150bbcec">another post</a> that has this bit:  "After watching forums, etc. for almost a year regarding this issue, we ran a MS Support Reque
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend