Transaction log full - how to overcome?

I have the following simple routine running once a week

TRUNCATE TABLE dbo.New_Business_Sales

INSERT INTO dbo.New_Business_Sales

SELECT * FROM [dbo].[vw_New_Business_Sales]

It fell over this week due to the error

The transaction log for database 'New Business' is full due to 'ACTIVE_TRANSACTION

This routine is quite intensive, I think there are several million records involved.

How do I get around the error? I have Googled and one piece of advice that kept cropping up was to insert the records 1000 at a time.....how would I achieve this?

Or, if anyone has any other suggestions, I'd be grateful.

Thank you as always.

If you are sure that the transaction log is full due to this statement, then may be try to do it in batches .
see this : http://michaeljswart.com/2014/09/take-care-when-scripting-batches/

1 Like

maybe change the "growth" of the Transaction Log so that it can extend dynamically, but beware it will not reduce in size, so once extended it will stay like that, even if the extra space is not needed. You CAN Shirnk it, but repeated Shrink/Re-Grow will fragment the file and be bad for performance. Also beware that it amy grow until it fills the disk ... :frowning:

If database is in SIMPLE recovery model do the import in batches as @stepson has described and/or set up dynamic file growth.

If the database is in FULL recovery model you will still need to import in batches / set dynamic file growth, but you will also need to ensure that the Log is backed up frequently. During heavy batch operations, such as this, we backup the Log file at one minute intervals, for the duration of the job (running it for longer than the job takes is OK, the downside is if you have to restore there will be more, individual, transaction log backup files to restore but, overall, the total filesize will be the same, so disk space usage will be "similar" either way.

1 Like

The transaction log is a circular file that's divided internally into a number of virtual log files (VLFs). SQL writes log entries sequentially into the VLFs. When one VLF fills up, SQL moves to the next one. When it reaches the end of the file, it will (ideally) go back to the beginning and start using the first VLF again (hence circular). for more see here:
http://www.sqlservercentral.com/blogs/zoras-sql-tips/2015/09/21/managing-sql-server-transaction-log-file/

1 Like