Menu
My log file in SQL Server used up all the space on my disk. I run full backups every night, but the log file keeps growing. What can I do?
427
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.
For Windows, you can use WordPad. If you have enough memory to cover the size of the file you want to edit, WordPad will load it. So these days, that's quite likely to apply to files even topping a gig in size. For Mac, use Vim.
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.
In some cases you might find the the logfile will not properly truncate even though a log backup is run. You can do a backup with TRUNCATE_ONLY to check it. When you run this it should truncate the transaction log:
BACKUP LOG dbname WITH TRUNCATE_ONLY
The cause of this problem is an open transaction in an earlier part of the log. SQL will not truncate the log past this transaction, potentially causing a large, ever increasing log. You need to find out which transactions are left open and why. You can monitor your log space with:
DBCC SQLPERF (LOGSPACE)
Information on long running transactions can be found using:
DBCC OPENTRAN
Or:
select * from sys.dm_tran_database_transactions
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
