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
Tags: bulk log, bulk logged, recovery model
Trackback from your site.
DotNetShoutout
| #
SQL-Articles ยป Script to get data file usage and autogrowth details…
Thank you for submitting this cool story – Trackback from DotNetShoutout…
VidhyaSagar
| #
Saeed,
I don’t think so you can stop it in SMO, instead you need to turn off password policy for that login
VidhyaSagar
| #
HI Naveed,
Could you please check the article wrote by deepak on fulltext? http://sql-articles.com/articles/dba/sql-server-2005-full-text-search/
VidhyaSagar
| #
Thanks Ashish, I’ve updated the script.
Ashish
| #
I was just searching some trace related articles and gone through your article. Found one incorrect information,
to disable the trace, the command should be
dbcc traceoff (….)
you might have by mistake mentioned it as traceon for disabling as well.