Limit Initials Log For Free

To reduce the physical size of a physical log file, you must shrink the log file. This is useful when you know that a transaction log file contains unused space. You can shrink a log file only while the database is online, and at least one virtual log file (VLF) is free.

The steps below can be used to shrink the size of SQL Server Transaction Logs. Use SQL Server Studio Management. Right click on the database with Transaction Log that needs shrinking. Select Tasks -> Shrink -> Files.

Transaction log backups not happening fast enough causes the SQL log file to become huge. SQL log files also enlarge due to incomplete replication or availability group synchronization.

Large database transactions, such as importing large amounts of data, can lead to a large transaction log file. Transaction log backups not happening fast enough causes the SQL log file to become huge. SQL log files also enlarge due to incomplete replication or availability group synchronization.

You can reduce the default size of an empty file by using DBCC SHRINKFILE target_size. For example, if you create a 5-MB file and then shrink the file to 3 MB while the file is still empty, the default file size is set to 3 MB. This applies only to empty files that have never contained data.

You should almost never need to shrink a transaction log if you are doing correct transactionlog backups. You will need to back up the database before you truncate the log. I suggest doing it on off hours so no new data is inserted between the backup and the truncation.

When log files keep growing and appear to be too big some might suggest switching to Simple recovery, shrinking the log file, and switching back to Full recovery. While this will work to shrink the file and free up disk space, it may grow right back again.