SQL Server databases created with two types of file:
Data File (File with extension .MDF/.NDF)
Log File (File with extension .LDF)
Data File can be one or more than one but physical log file will always remain one for each database. You have control over the size of log file but you can’t have more than one physical log file.
There are so many mysteries and myth roaming around log file in developer’s mind so I thought to take an initiative to break this ice.
What is the need of Log File?
Log file plays an important role for SQL Server databases. Any DML or schema change TSQL you apply, it first goes to log file and later on it will be save to data file. By having this kind of architecture, SQL Server ensures your data integrity. If anything wrong happens to your TSQL command and it doesn’t get finished then SQL Server can ROLLBACK it or even user can do it too if transaction was used. Because of LOG (Transaction Log) file, SQL Server guarantees ACID (Atomicity, Consistency, Isolation, and Durability).
Before we move further, it is worth to look at the definition of ACID.
Atomicity ensures that either all operation in one transaction apply or none. It means that either 100% COMMIT or 100% ROLLBACK.
Consistency ensures that a transaction either creates a new and valid state of data, or returns all data to its state before the transaction was started. It means that you have guarantee that a transaction never leaves your database in a half-finished state.
Isolation ensures transaction separation until they are finished.
Durability ensures that the data is saved by the system such that, even in the event of a failure and system restart, the data is available in its correct state.
Since we can have more than one database data file, we can either keep it on one drive or may distribute it over multiple disks as per our requirement of performance as well as disk space but we can have only one transaction log file (.LDF) so it is necessary to have proper size of .LDF file based on database requirement.
Note: Though we have only one transaction log file, we can have multiple Virtual Log File (VLF) inside our one transaction log file. This is different topic and out of the scope of this article so I will come up with separate article on this topic.
There is no rocket science which suggests you exact size of your database transaction log file. It is completely up to the requirement of business which vary from case to case. All I can suggest is neither keep log file too big then your requirement nor keep it too small.
Bigger transaction log file takes time to recover and somehow it affects the overall performance of database too at the same time, smaller log file size resulted into frequent trigger to auto grow transaction log file size.
Prevent transaction log file from frequent auto grow; keep its size reasonably big to accommodate your business transaction. No matter how big transaction log file you have, it used to be filled completely sooner or later and at that time, you have no option other than either to increase transaction log file size or truncation log file.
I personally against both the way I have mentioned in above paragraph. We shouldn’t truncate log file, not at least if we have other alternative because truncating the log file increase the risk of break up in log backup chain.
I would suggest taking transaction log backup frequently which helps you in two ways:
1.) You can restore your database in point-in time whenever needed
2.) It free-up unused part of log file so that new transaction can use that free space inside transaction log file and you don’t need to grow it much
Last but not the least, we have three recovery mode (Simple, Bulk-Logged and Full) for database which affect our transaction log file very much.
Simple: This recovery mode don’t use transaction log file too much. It writes the data in transaction log file and as soon as checkpoint runs and data transfers to data file, it truncate transaction log automatically so there is no possibility to take transaction log backup and hence in time of crisis, we can only restore the last full backup of data base available.
Bulk-Logged: This recovery mode uses transaction log file and log all DML operation except BULK Insert, BCP, CREATE INDEX, SELECT INTO and text & image operation. We can take transaction log file backup in this mode. In time of crisis, we can have full database backup and all transaction log backup we have.
Full: This is one of my favorite recovery modes for mission-critical production database which logs each and every transaction. It helps to achieve Point-In time recovery.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations