|
You can say a lot about transaction handling, transaction logging, recovery, various backup types and restore scenarios etc. The purpose of this article is not to dwell on such subjects - that would make this article huge.
How to manage the ldf file
SQL Server logs every modification to the ldf file, for several reasons. You have two options for how to make sure that the ldf file doesn't grow indefinitely:
Set recovery model for the database to simple. Do this if you don't want to take transaction log backups.
Set recovery model to full. Do this if you do want to take transaction log backups.
Yes, it really is that simple, so re-read above points again! So, there are two ways to go wrong:
Have simple recovery and attempt to do a log backup. Result: You will get an error message from the BACKUP LOG command.
Have full recovery and don't do log backup. Result: ldf file will just grow and grow, endlessly! This is a very common cause for large ldf files.
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. This is often referred to as "truncate the log", or "empty the log". If you have simple recovery, then it is no longer your responsibility to "empty the log", instead SQL Server will do this by itself. However, old open transactions or long running transactions will set a limit for how much can be emptied in the log. So, it is perfectly possible to have large ldf files in simple recovery model.
Your recovery requirements
It is imperative that you match your backup and restore strategy to your recovery requirements. This cannot be stressed enough.
How much data can you afford to lose? Sometimes referred to as Recovery Point Objective (RPO).
For how long can you accept your database to be unavailable? Sometimes referred to as Recovery Time Objective (RTO).
This is a whole topic in itself. Here you find a good primer on the topic of RPO and RTO.
So 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.
How large should the ldf file be?
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). Long running transactions taken into account.
Problem with above is that the answer isn't very helpful. So allow me to generalize a little bit:
If you are in simple recovery model, then just shrink the file! If the file doesn't shrink to the size you want, then try again, and again. If it still doesn't shrink to the size you want, then read this (the "Shrinking of transaction log file" section).
If you are in full recovery model, and this is where you want to be, then you have to make a decision:
Is it OK to break the transaction log backup chain?
By this we mean the chain of log backups you may have. If you don't have any prior log backups (perhaps not even a prior database backup), then it is OK to break the chain of log backups. If you do have earlier log backups and want to be able to restore from them up to any point in time then it isn't OK to break the chain of log backups.
OK to break the chain of log backups. This is easiest:
Set recovery model to simple.
Do the shrink according to the steps above (for a database in simple recovery model).
Set recovery model to full.
Perform a full database backup, so your scheduled log backups have somewhere to begin from.
Not OK to break the log backup chain. Then you backup the log (which can produce a huge log backup file, and consequently take some time), and then shrink the file. If the file doesn't shrink to the size you want, then backup log and shrink again, and again. If it still doesn't shrink to the size you want, then read this (the "Shrinking of transaction log file" section).
|