SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Is it good idea to change Simple Recover Model int
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bugarl
Starting Member

1 Posts

Posted - 08/12/2014 :  17:55:18  Show Profile  Reply with Quote
We are having an ETL process which inserts lots of data into tables. This database is set to Simple Recovery Model and the transaction log is growing a lot. I was thinking that would it help to set this database into Bulk-Logged Recovery Model? We are taking full backups on daily basis. So is there some operations which are not logged in Bulk-Logged Recovery Model compared to Simple Recove

igormalse
Starting Member

3 Posts

Posted - 08/13/2014 :  03:50:22  Show Profile  Reply with Quote
Bulk Logged will not help you in this situation. Your transaction log is growing because you are performing large transactions. In simple mode, the log is truncated after a checkpoint. Check these site: http://www.sql.recoverytoolbox.com/ & download demo Recovery Toolbox for SQL Server. Depending on how you are loading your data, you may need to break it up into smaller chunks. Make sure you are not loading all the data in one large transaction. Read more: http://www.filerepairforum.com/forum/microsoft/microsoft-aa/sql-server/380-is-it-good-idea-to-change-simple-recover-model-into-bulk-logged-on-etl-system

Edited by - igormalse on 11/06/2014 10:16:05
Go to Top of Page

Lincolnburrows
Starting Member

43 Posts

Posted - 08/13/2014 :  07:15:42  Show Profile  Reply with Quote
Each recovery model has its unique properties which makes distinguishes between them. Simple recovery model doesn’t include the backup of log files if you with Bulk-logged recovery model it uses transaction log files to maintain and stores the whole operations which saves processing time and whole process is knows as Minimal Logging. Bulk-logged recovery model performs high-performance bulk copy operations; minimal log space is used by bulk operations.

I would like to recommend you to use Simple recovery models if some features fails to support Bulk-logged recovery mode
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2076 Posts

Posted - 11/11/2014 :  01:53:41  Show Profile  Visit jackv's Homepage  Reply with Quote
Analyse the ETL process and quantify the size of the recordsets , if it makes sense , commit transaction more regularly. Read this post on architecting write-intensive databases - http://www.sqlserver-dba.com/2011/06/architecting_write_intensive_databases.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000