Judging Microsoft Imagine Cup (Final Round) – 2017 in Bangladesh
Venue: Microsoft Office, Dhaka, Bangladesh
Date: 06 April, 2017
In various organizations, huge SQL databases are equipped, which perform more than millions of transactions per hour. A SQL server database has data files and transaction log files. Data files store the user data and transaction log files store all the changes user made in the database and all the details of the transactions performed while making changes.
Now, the issue is that this feature of logging the details, every time changes are made in SQL server can not be controlled or stopped. This causes a grave issue when the size of SQL server overgrows. However, the way in which these log files grow and configure can be controlled. So, to avoid SQL server log file growing unexpectedly, consider any of the following methods given below. Also, to manage the large area size, it is good to shrink log file size, we will discuss the ways to resolve the same issue in this content.
There are numerous ways for truncating sql ldf too big file. Some of the chief solutions have been provided in the following segment of this content.
In the simple model, the most recent backup of the database of SQL server is recovered while in the bulk-logged or full recovery model, database can be recovered up to the point of failure. This recovery is done by restoring transaction log file.
By default, Full recovery model is set. Then, user has to regularly back up the transaction log files to prevent them from becoming too large and to remove inactive transactions from transaction log. Also, consider this when taking back up of .ldf files.
NOTE: If user is defragmenting indexes, make use of DBCC INDEXDEFRAG and not DBCC DBREINDEX. If DBCC DBREINDEX is used then, transaction log file might expand drastically.
If maintenance is not carried on regularly, log file size grows too big. Therefore, it is recommended to take these manual steps before it takes up all available disk space. Now, let us look at the method to shrink a SQL Database’s transaction log file:
NOTE: User may repeat steps 3,4 and 5 until the .ldf file size becomes physically smaller.
In this content, we have discussed various solutions on how to truncate .ldf files if transaction file size becomes too large. This is necessary in order to manage data with ease. Manual method and some general solutions have been written in this content to enlighten users when they come across such issues while dealing with SQL Server.