May 20, 2008 at 3:31 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 7:54 am
If you do a search on Recovery Models for SQL Server, you should find several good sites that will answer most of your questions.
See following link for shrinking log file:
http://support.microsoft.com/default.aspx?scid=kb;en-us;873235
Full recovery model can impact performance since there's additional overhead of writing to log file, however, if you require full protection in case of disaster, then you'll want to use this recovery model.
May 20, 2008 at 9:22 am
You should never shrink the data files if you can avoid this.
Shrinking the t-log does not invalidate the backup chain. http://msdn.microsoft.com/en-us/library/ms190749.aspx
The log file does not shrink after a log backup. You perform a backup, the space is then freed internally to be re-used, but there is no external file size change. The size should be set to prevent log growths for normal operations.
What is large? The backups might seem large, but they are based on the changes to your data and they are important for recovery.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 20, 2008 at 2:50 pm
Ah, one of my favorite subjects, backups. What you should be doing is what your business/SLA wants you to do. How much data loss can you tolerate, an hour, a day, a week? How much downtime is allowed for recovery, an hour, two? How much data are you inserting/deleting/updating? These will all give you a better picture as to what recovery model you should be in. Lots of transactions and low tolerance for data loss would indicate full recovery with frequent log backups/differential backups. Losing a full day's worth of data acceptable? Simple mode with full nightly backups could work (or a weekly full and nightly differentials). You need to determine what the expectations for your business are as there are a variety of combinations that may work. And as Steve pointed out, shrinking the file (don't do it!) won't break the chain but backup log with truncate_only will. BOL has more details on all the backup options available to you. Post back the answers and we'll point you in the right direction.
-- You can't be late until you show up.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply