question

MakipR avatar image
MakipR asked

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?
backup-restoredisk-spacefull
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you.
0 Likes 0 ·
Ashish Kumar Mehta avatar image
Ashish Kumar Mehta answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Naren avatar image
Naren answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.