How to Prevent SQL Server LDF file growing unexpectedly problem?

Each database in SQL server contains at least one data file and one transaction log file. SQL server database stores data physically in the data file. A transaction log file contains all the modification related information performed on the database. It is logically divided into small segments that are known as virtual log files. Database administrator is responsible to maintain the size of transaction log file. Sometimes, size of transaction log file grows unexpectedly because database administrator has not planned for the transaction log files grow.

I have seen several related questions on the different forums like how to shrink transaction log file? My transaction log file is growing unexpectedly, what to do? How to reduce the size of transaction log file?  Before going to solve these problems, first we will discuss about how to prevent this problem because prevention is the best method to avoid problems. Following points are very helpful to prevent transaction log file growing unexpectedly….

• Avoid auto grow of transaction log file.
• Take back-up of transaction log file regularly and delete the inactive transaction log file of your database.
• Stop the uncommitted running transaction.
• Design the transaction log file to be small.
• Change the recovery model; there are three types of recovery models available in the SQL server, simple, bulk logged and full recovery model. Simple recovery model automatically shrink the transaction log file of your database while bulk-logged and full recovery model does not. If you use simple recovery model then you can recover SQL database from most recent backup. By the using of bulk-logged & full recovery method, you can recover your database to the point of failure.  

If you have followed above points before creating your database then I am quite sure you will not face above discussed problems. In case still facing same problem then run below command to shrink your transaction log files.

DBCC SHRINKFILE
(
 {File name | file id}
{[, emptyfile] | [[, tragetsize] [, {notruncate | truncateonly}]]}
)
[With no-infomsgs]