0

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?

5
  • Perform your insert/update/delete in small chunks for example 100.000 rows at a time. Shrinking log cannot help you as SQL Server truncates it as soon as possible in simple recovery model and if it cannot do it is just because of a long running transaction
    – sepupic
    Apr 2 at 10:56
  • Thanks sepupic. If I do things in chunks do I need to wrap them in Begin Tran and Commit Tran or will that happen automatically in simple recovery mode?
    – Andy
    Apr 2 at 19:45
  • 1
    9m rows is tiny, why does this need to take 2.5 hours, and why does it need a bunch of UPDATEs, why not a single query with a bunch of joins? Apr 2 at 22:35
  • @Charlieface. 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 clients server only takes 45 mins with 11m rows.
    – Andy
    Apr 5 at 11:15
  • @Charlieface. And 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.
    – Andy
    Apr 5 at 11:24

1 Answer 1

0

... 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 will 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.

3
  • Okay, thanks Phill. I'll give that a go. It doesn't need to be done in one huge transation, it's just what I inherited.
    – Andy
    Apr 2 at 19:46
  • 1
    "Your database will do this by default if you don't tell it different" please back that up with something because I'm pretty sure that's not true unless it's a single statement. Apr 2 at 22:34
  • @Charlieface Indeed, that statement is a bit inaccurate. Generally each statement is its own implicit transaction until wrapped together in an explicit transaction by the developer. So if there are multiple steps, each as a separate DML statement, in this stored procedure, they typically would not implicitly run as a single transaction.
    – J.D.
    Apr 3 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.