Overview

In our previous section we have discussed how to create an audit trigger in SQL Server and how it helps for database security in different aspects and also knows how harmful it is for a database. Now, in this section, we will discuss SQL Server log file and its uses with its different components. It is not an easy task to tackle with database problems. If you are the database administrator, you need to know all about database.

Luckily, SQL Server creates various different log files which can help us to solve various problems like setup issues and application errors. Let’s discuss SQL Server Log File.

What is a Log File in SQL Server?

A log file is a database file in SQL Server, which stores all the log information and uses .ldf extension. The information that a log file stores can be helpful at the time of data loss or recovery. A log file stores all that events which are being executed on a database. The size of a log file depends on its logging level that a user sets in his database. It is necessary to have at least one log file in SQL Server database. The main reason for using a log file in SQL Server is to restore the database if a system failure occurs.

SQL Server Transaction logs

Every modification in SQL Server count as a transaction and these transactions are stored in a transaction log file in a database. The transaction logs are very important part of SQL Server database because they can be helpful in various circumstances like forensics of the database. For example: if a third person tempers on a person database, in that case, the transaction logs can be used to track what changes that third person has made in the database. Also, if a system failure occurs, the transaction logs can be required to bring the database back to a consistent state.

How to manage the size of a log file?

However, the logging level affects to log file’s size. So it can be useful to physically shrink or expand the log file of a SQL Server database. To maintain the size of a log file you must be truncate the transaction log on a regular basis to keep it from filling up. However, it can be possible to delay some factors such as log truncation.So it is important to monitor log size. Also, you need to avoid some operations that are not more useful for your database, but the impact of those operations increasing the size of your log file.

For managing the size of a log file we need to perform some basic operations that are mentioned below:

• Monitor Log Space Use
• Shrink log file size
• Add or Enlarge a Log File
• Optimize the Size of the tempdb Transaction Log
• Control the Growth of a Transaction Log File

Monitor Log Space Use: The log space use can be monitored by using DBCC SQLPERF. This command is useful and returns the information about the log space amount that is currently used also it indicates if the transaction log is filling up and is need of truncation. It is recommended that to avoid overloading the log disk.

Shrink Log File Size: The shrinking log file operation applied when you want to reduce the physical size of a physical log file. This is helpful when you don’t need unused space and you know the transaction log file containing unused space. This occurs only if the database is online, and at least one virtual log file is free. However, in some cases, shrinking the size of the log file may not be possible until after the next log truncation.

Add/Enlarge Log File: Another method to gain extra space is to enlarge the existing log file, if the extra disk space is available or you can add secondary log file to your database on a different disk.

• To add another log file to your database, you can use ADD LOG FILE clause of the ALTER DATABASE statement. This allows the log to grow.
• To enlarge the size of the log file you can use MODIFY FILE clause of the ALTER DATABASE statement, this will specify the SIZE and MAXSIZE syntax of the log file.

Optimize tempdb Transaction Log Size: In this method you need to restart your SQL Server instance, this will resize your tempdb transaction log to its original size and reduces its performances. Also, this can be avoided by increasing its size after starting or restarting the SQL Server instance.

Control Transaction Log File Growth: To control transaction log file growth, the ALTER DATABASE command can be used to manage the growth of a transaction log file. Note the following:

• Use, size option to change the existing file size
• Use FILEGROWTH option to change the growth increment.
• Use MAXSIZE option to set growth to UNLIMITED.

Log File Operations when Transaction log growing

It is possible that an SQL Server can have more than one log file, And Adding more than one transaction log files will not change the performance of the SQL Server database. Modification can be done only into one file at the time.

Having more than one transaction log files is recommended when the first one is full or if the disk drive is running out of space.

While making the changes in the database, the logging level is automatically growing. In such situations, it is highly recommended to maintain this logging level by applying one of three recovery models.

• Simple recovery Model- In Simple recovery model, those Operations are not supported which require transaction log backups
• Full recovery Model- This recovery model requires log backups and the work is affected due to a damaged data file.
• Bulk-logged Model- Requires. This recovery model is an auxiliary model of the full recovery model which requires log backups, that allows high-performance bulk copy operations.

So these three recovery models are responsible when a transaction log file is growing.

Conclusion:

After reading about SQL Log File, we know, how a SQL Server log file is important for the database, which saves all the transactions that are performed in a database and also If a database going to a consistent state, then, the only log file comes into existence. However, it is also possible that if a power failure occurs the log file can also be corrupt. So, in such scenario the SQL Server log file analyzer tool is highly recommended, which helps, you can read and analyze your SQL Server log file.