Truncating a log file in sql

Truncating a transaction log file means clearing out space in the log file. The transaction log file is automatically truncated in simple recovery model and when log backups are performed. A log backup removes the inactive portion of the logs thus freeing up space in log file. It is not recommended to forcefully truncate transaction log file.

It may break the log backup chain. However, when absolutely necessary it can be done as shown below. There can be data in the log not yet committed to the data file. You would lose such data. If, in your answer, you warn not to try it in production, it's not worth posting at all.

I disagree with the downvoters -- it is an option. Administrators just need to understand their scenario. For example -- there will be no 'uncommitted' data if there are no open transactions.

This is the only solution that worked for me. My drive was filled up and I couldn't backup or shrink and nothing else seemed to be working. I agree; it's not a best practice but it's valuable a tool to have if you have no other options such as Brian's scenario. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Stack Gives Back Safety in numbers: crowdsourcing data on nefarious IP addresses.

Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually. Linked 3. Related Hot Network Questions.

Question feed. Stack Overflow works best with JavaScript enabled. Accept all cookies Customize settings. Note that minimal logging is not used when existing values are updated. If the database is set to the simple or bulk-logged recovery model, some index DDL operations are minimally logged whether the operation is executed offline or online. The minimally logged index operations are as follows:.

Index build operations use minimial logging but may be delayed when there is a concurrently executing backup. This delay is caused by the synchronization requirements of minimally logged buffer pool pages when using the simple or bulk-logged recovery model.

Manage the Size of the Transaction Log File. Skip to main content. This browser is no longer supported. Download Microsoft Edge More info. Contents Exit focus mode. Is this page helpful? Please rate your experience Yes No. Any additional feedback? Warning Never delete or move this log unless you fully understand the ramifications of doing so.

Tip Known good points from which to begin applying transaction logs during database recovery are created by checkpoints. Note Log truncation does not reduce the size of the physical log file. Note Under the full recovery model , all bulk operations are fully logged.

Note Index build operations use minimial logging but may be delayed when there is a concurrently executing backup.

Submit and view feedback for This product This page. View all page feedback. In this article. Currently there are one or more reusable virtual log files VLFs. No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file VLF. All recovery models This is a routine reason for delaying log truncation. A log backup is required before the transaction log can be truncated.

Full or bulk-logged recovery models only When the next log backup is completed, some log space might become reusable. A data backup or a restore is in progress all recovery models. If a data backup is preventing log truncation, canceling the backup operation might help the immediate problem. A transaction is active all recovery models : A long-running transaction might exist at the start of the log backup.

In this case, freeing the space might require another log backup. Note that long-running transactions prevent log truncation under all recovery models, including the simple recovery model, under which the transaction log is generally truncated on each automatic checkpoint. A transaction is deferred. A deferred transaction is effectively an active transaction whose rollback is blocked because of some unavailable resource.

For information about the causes of deferred transactions and how to move them out of the deferred state, see Deferred Transactions SQL Server. Long-running transactions might also fill up tempdb's transaction log.