Is it good idea to change Simple Recover Model into Bulk-Logged on ETL system? RRS feed

  • Question

  • 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 Recovery Model?
    Tuesday, August 12, 2014 5:49 PM


All replies

  • Hi bugarilenet,

    Since this is SQL Azure database forum, so we need to verify if you want to change the recovery model on SQL Azure database. If yes, it will fail when change the recovery model to Bulk-logged, or Simple from Full model. In Windows Azure SQL database, the Recovery model is set when the master database is created and cannot be modified because the ‘master’ database is read only for anyone but Microsoft. For more information, see: http://alexandrebrisebois.wordpress.com/2013/02/27/windows-azure-sql-database-cannot-be-set-to-simple-recovery-model/

    In addition, if it is on premise SQL database, in simple and bull-logged recovery models some operations can be minimally logged, For example in Simple the older non-active transactions are overwritten when SQL reaches the end of the transaction log, however,in bulk-logged and full you need to back up the older non-active transactions before the older records can be overwritten. There is more detail about Database Recovery Models in SQL Server, you can review this article.


    Sofiya Li

    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Tuesday, August 19, 2014 2:34 AM
    Wednesday, August 13, 2014 2:46 AM