x

Switching from SIMPLE to FULL - Space impact?

I am considering switching from SIMPLE to FULL recovery model in order to support backup/restoring different Filegroups.

What are the space (and other resource) implications from switching from SIMPLE to FULL recovery For example, does the ldf file get larger?
more ▼

asked Nov 08, 2011 at 10:16 AM in Default

MakipR gravatar image

MakipR
1 2 2 2

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Swapping from SIMPLE to FULL will bring a few changes to your system.

First of all, you will now need to run LOG backups. Previously (with SIMPLE) you didn't need to do this at all. This is a requirement to make sure that the changes you have committed to the database are "hardened" in the data file. Pay attention to this, especially how often you run a backup. If you have lots of data movement (inserts, updates, deletes) in the database, the volume of data in the log file will be greater. Without log backups the data changes will remain in the log file and the LDF file will continue to grow.

The next part to consider is that you now have the ability to perform a point in time recovery. This may not be a necessity for you (you mention filegroup restores being the reason for FULL recovery), but is the main reason for using the FULL recovery model.

With the Log file backups required with the FULL recovery model, you also need to consider the additional hard disk space needed to hold backup files. The additional overhead of administration of backups/restores is another point to consider.

Take a look at the [Recovery Model Overview on MSDN][1] to see a little more detail on each recovery model.

[1]: http://msdn.microsoft.com/en-us/library/ms189275.aspx
more ▼

answered Nov 08, 2011 at 11:08 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

Thank you.
Nov 09, 2011 at 10:53 AM MakipR
(comments are locked)
10|1200 characters needed characters left

I would recommend you to read [Understanding SQL Server Recovery Model][1] article which explains three different types of recovery models available in SQL Server and when to use a specific recovery model.

[1]: http://www.mytechmantra.com/LearnSQLServer/SQL_Server_Recovery_Model.html
more ▼

answered Nov 09, 2011 at 02:22 AM

Ashish Kumar Mehta gravatar image

Ashish Kumar Mehta
1

(comments are locked)
10|1200 characters needed characters left

Simply the main difference in two recovery models. In SIMPLE log file inactive portion is truncated automatically by truncate logon check point. So data is not available in that inactive portion so we are not able to take a log backups. So if we don't have log backups we are not able to do point in time and point of failure restoration.

Full recovery model the inactive portion of log file is not truncated automatically. So we can able to take a log backups. When we are taking the log backup need maintain those files in hard drive space is impact. While taking a log backup the inactive portion of ldf file is truncated and that space is free. So we have a chance to do pint in time and point of failure restoration.
more ▼

answered Nov 10, 2011 at 03:34 AM

Naren gravatar image

Naren
27 17 18 19

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x47
x6
x4

asked: Nov 08, 2011 at 10:16 AM

Seen: 1218 times

Last Updated: Nov 08, 2011 at 10:16 AM