Avoid logfile getting too large on SQL Server

Question

I have a SQL Server stored procedure that essentially 'flattens' a complex database into a single, simplified, table in a separate database to allow users to query the data without needing to understand the database structure.

The SP truncates the destination table, inserts the primary key and other fields and then performs a series of updates to populate all of the other fields in the table using user functions and various queries to format the data.

There are approx. 9 million rows in the table and the SP takes about 2.5 hours to run and during that time the log file grows to over 30GB. The database is on an Azure server with limited disk space and recently the SP crashed as the log file ran out of space.

The target database is in simple recovery mode and the SP runs over a weekend so there are no other processes or users using it. The SP doesn't contain any transactions or commits and doesn't chunk up the work, it just runs through from start to end inserting, updating (and occasionally deleting) the rows.

How can I make it more efficient and use less log file space? Should it be using transactions, commits or shrink the log file as it runs? Or something else?

---

It takes 2.5 hours because there are a lot of steps and it's also running on a low-spec (and hence cheap) Azure server. A similar script running on another client's server only takes 45 mins with 11m rows.

It needs a whole bunch of updates because it is reformatting and 'cleaning' some of the columns. For example, rich-text comments fields are stripped of CRs and LFs, have sensitive data within square brackets removed, etc. It compresses the rows from 40+ tables into just 2 tables and it can't do that in a single statement with 40+ table joins.

Answer 1

... inserts the primary key and other fields and then performs a series of updates to populate all of the other fields in the table using user functions and various queries to format the data.

Does all that need to be done in one, huge Transaction? Your database may do this by default if you don't tell it different. Remember that everything you do in a Transaction has to fit into the available "log space". As soon as you issue a commit, though, that "log space" becomes useable again.

Depending on how much work each "step" does, you could do all the inserts, commit those, then do the updates, possibly in "chunks", committing each as you go along. Putting in the commits means you shouldn't run out of transaction log space.

If the whole process fails, you would throw the "other" database away and rebuild it from scratch.