Post reply

BulKogged Vs Simple

  • March 13, 2008 at 1:08 am

    #122695

    Hi,

    Want to know the advantages/disadvantages of the Bulk logged recovery model vis a vis the simple recovery model.

    Learnt this much from BOL:

    Bulk Logged Simple

    Point in time recovery possible Not possible

    Bulk operations minimally logged Not logged at all

    T Log backups can be taken Cannot be taken

    Apart from all these differences, i haven;t come across many people using the BL model as compared to the other 2 models...

    Why is that??

  • March 14, 2008 at 11:04 am

    #789767

    I'd recommend reading this, gives a nice easy to follow comparison:

    http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1069109,00.html

    Regardless, what is your goal here? Is your database highly transactional? Is performance an issue? Do you need the ability to restore to a point in time?

    It is easier to list what it is you want your database to do, and then pick the appropriate options.

  • March 14, 2008 at 11:42 am

    #789791

    Select Simple if:

    * Your data is not critical.

    * Losing all transactions since the last full or differential backup is not an issue.

    * Data is derived from other data sources and is easily recreated.

    * Data is static and does not change often.

    * Space is limited to log transactions. (This may be a short-term reason, but not a good long-term reason.)

    Select Bulk-Logged if:

    * Data is critical, but logging large data loads bogs down the system.

    * Most bulk operations are done off hours and do not interfere with normal transaction processing.

    * You need to be able to recover to a point in time.

    Select Full if:

    * Data is critical and no data can be lost.

    * You always need the ability to do a point-in-time recovery.

    * Bulk-logged activities are intermixed with normal transaction processing.

    * You are using replication and need the ability to resynchronize all databases involved in replication to a specific point in time.

    SQL DBA.

  • March 16, 2008 at 11:50 pm

    #790218

    Thanks everyone for their reply

  • March 17, 2008 at 6:44 am

    #790297

    The diference is that BulkLogg don't log the process for lotes into the transaction log.

    process for lotes(select * into from tabla, insert into, BCP...etc.)

    Is good practice use bulklogg when this command are use frecuently in our enviorment(insert into, select into ).. this keep the transtion log manageable.

    Greetings,

    Santiago Carela

    Rep. Dom.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply