May 20, 2008 at 3:30 am
Hi All,
I am thinking of changing the recovery model of my database to full recovery model, on doing a test on another database, I noticed that the log backups tend to be huge in size, the transaction log file size hasn’t also decreased, even after a log file backup and also a differential database backup.
In view of this, I will like to ask the following questions:
1.What are the do’s and don’ts of changing database onto full recovery model and doing full database backups, differentials and transaction log backups.
2.I have also heard that one should never shrink the transaction log, as this invalidates the backup chain, if this is true, what should one do if the log file is growing/has grown to an unacceptable size.
3.If constant BCP operations are being done on the database, what are the consequences of changing to bulk logged, will one have the same level of protection as fully logged.
4.Does the full logged recovery model impact performance ?
I will be looking forward to hearing from you.
Thanks.
May 20, 2008 at 4:16 am
Whats tour database's present recovery model.
May 20, 2008 at 4:35 am
Its simple, but i am looking at changing this to provide us with more backup options.
May 20, 2008 at 4:54 am
Dean Jones (5/20/2008)
Hi All,I am thinking of changing the recovery model of my database to full recovery model, on doing a test on another database, I noticed that the log backups tend to be huge in size, the transaction log file size hasn’t also decreased, even after a log file backup and also a differential database backup.
In view of this, I will like to ask the following questions:
1.What are the do’s and don’ts of changing database onto full recovery model and doing full database backups, differentials and transaction log backups.
2.I have also heard that one should never shrink the transaction log, as this invalidates the backup chain, if this is true, what should one do if the log file is growing/has grown to an unacceptable size.
3.If constant BCP operations are being done on the database, what are the consequences of changing to bulk logged, will one have the same level of protection as fully logged.
4.Does the full logged recovery model impact performance ?
I will be looking forward to hearing from you.
Thanks.
1. Do make sure that you do a full backup before and after the switch, also make sure that once you are in full mode you implement log backups, otherwise your log will just grow and grow.
2. You dont need to shrink the log normally, if you do then sql will probably auto grow to that size again anyway causing performance issues. If the log size is growing too large increase the frequency of the log backups
3.Yes you will have the same protection as in full recovery model, however you will only be able to recover to point in time if the log doesnt contain bulk logged operations. see this BOL link ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/4cd5dfb7-2d44-4544-956e-4daf3879cfe3.htm
4.Apart from more disk space required/tlog backups needing to be run, it doesnt effect performance.
I may not have explained number 3 correctly/very well, best off reading BOL for the "correct" explanation.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply