All about Bulk Logged
This document is relevant to bulk operations on SQL Server. The bulk-logged recovery model is a special-purpose model that works in a similar manner to the full recovery model. The only difference is in the way it handles bulk data modification operations. The bulk-logged model records these operations in the transaction log using a technical known as minimal logging. This saves significantly on processing time, but prevents you from using the point-in-time restore option.
Microsoft recommends that the bulk-logged recovery model only be used for short periods of time. For best practice I had created a DB name called bulk Operations and Created couple of tables with two columns,
I had executed the same kind of bulk insert operations twice and changing the recovery models as FULL and Bulk Logged. And captured the log file growth,
- Initial size of Log File
- After 8000 Rows Insertion [by using ordinary insert method]
- Bulk Insert [by using select into clause]
- bulk Insertion by using ordinary select
- bulk Insertion by using BCP
- bulk update
- Create Index
Conclusion
Microsoft recommends the bulk-logged recovery model only is used for short periods of time. Its much suitable for processes like Select into clause, Index (Create / Rebuild/ Re-Organize) operations.
Reference
http://msdn.microsoft.com/en-us/library/ms190692.aspx
http://www.sql-articles.com/articles/troubleshooting/log-file-growth-in-sql-server
All about Bulk Logged,Tags: bulk log, bulk logged, recovery model
Trackback from your site.
Engin Ardıç
| #
Thank you for nice article! That’s so helpful for me.
VidhyaSagar
| #
Sure will plan something when you come here
Mohamed Kabiruddin
| #
Hi Vidhysagar & Team,
I am happy to hear that the SQL Server User Group in Chennai is active and going strong. I came across your blog and wanted to attend one of your user group meetings.
I am basically a Chennai-ite and work as a Microsoft BI Consultant in Australia. I am an active member of PASS SQL Team in Australia and have also presented for PASS SQL Saturdays in Australia. I visit Chennai almost every year for holidays and I would love to attend one your sessions. I am happy to speak on any particular topic of interest and discuss case studies on some of the projects and the industry in Australia as well.
I recently presented on “Agile BI with SQL Server 2012″ for PASS. I am coming down to Chennai this August 2013 for a fortnight. Please feek free to get in touch.
Cheers,
Mohamed Kabiruddin
admin
| #
@aj — Both the scripts are same. In my example I’m passing the output value to a variable. In your example you aren’t passing it instead you make the task failure or success.
ajssis
| #
You are creating a lot of extra steps that are not required in the Script task. You fail to see the line right after your code that is
Dts.TaskResult = (int)ScriptResults.Success;
The opposite of this is
Dts.TaskResult = (int)ScriptResults.Failure;
The code you should be using is
// Check if the file exists
if (File.Exists((string)(Dts.Variables["FilePath"].Value)))
{
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
If the file exists then the Script task result will be “Success”. If the file does not exist the Script task results will be fail.
Then all you do is set the Success direction flow (the green line) to go to your data flow task. If the result is Failure then the Failure direction flow (the red line) will go to your Update error task. Be sure to RIGHT-CLICK the direction flow and select “Failure” from the pop-up menu.