Then don't be surprised if you need an ldf file which is about 85 GB. Say you have a data file of 100 GB, filled to 85 GB. In full recovery, you might just need as large ldf file as the amount of data you have.So if you find that the ldf file keep growing to a certain size, then just leave it at that size! But you still need to accommodate your largest transactions. In simple recovery model, you can frequently get by with a small log file (about 5-10% of the database size).Problem with above is that the answer isn't very helpful. Long running transactions taken into account. Large enough to accommodate the log records produced between your log backups (or whenever SQL Server itself will empty the log, in simple recovery model). How long is a piece of string? The technically correct answer to above question is: Don't just set the recovery model to simple in order to keep the ldf file small! Make sure you have a recovery model, backup strategy and restore strategy that supports your RPO and RTO. Here's a good primer on the topic of RPO and RTO. For how long can you accept your database to be unavailable? Sometimes referred to as Recovery Time Objective (RTO).How much data can you afford to lose? Sometimes referred to as Recovery Point Objective (RPO).It is imperative that you match your backup and restore strategy to your recovery requirements. So, it is perfectly possible to have large ldf files in simple recovery model. However, old open transactions or long running transactions will set a limit for how much can be emptied in the log. If you have simple recovery, then it is not your responsibility to "empty the log", SQL Server will do this by itself. This is often referred to as "truncate the log", or "empty the log". When you do a log backup, SQL Server will mark space in the ldf file as "re-usable", so that space can be re-used by subsequent log records that are produced. Result: ldf file will just grow and grow, endlessly! This is a very common cause for large ldf files. Have full recovery and don't do log backup.Result: You will get an error message from the BACKUP LOG command. Have simple recovery and attempt to do a log backup.Yes, it really is that simple, so re-read above points again! So, there are two ways to go wrong: Do this if you do want to take transaction log backups. Do this if you don't want to take transaction log backups. Set recovery model for the database to simple.You have two options for how to make sure that the ldf file doesn't grow indefinitely: SQL Server logs every modification to the ldf file, for several reasons. The purpose of this article is not to dwell on such subjects - that would make this article huge. You can say a lot about transaction handling, transaction logging, recovery, various backup types and restore scenarios etc. The purpose of this article is to explain: You probably read this because you have a database with a large transaction log file (.ldf).
0 Comments
Leave a Reply. |