Given a SQL Server table with

  • a large number of rows
  • no columns with large-value data types
  • multiple indexes
  • more allocated space than available for the largest possible transaction log size
  • a single-column primary key with clustered index (optional consideration for this question)
  • an average record size of 1k (optional consideration for this question)

and an update statement which

  • needs to be run against every row
  • sets a value on a non-indexed column (optional consideration for this question)

What techniques can be employed to reduce the peak disk space consuption (including data files, log file and tempdb - if applicable) required to do this update?

For purposes of this question, the following is allowed:

  • applying changes in batches
  • run in single-user mode
  • change recovery model

3 Answers 3


I've just gone through a similar process just couple of weeks ago. After several tries, with couple of the bigger tables (one of them more than 100million rows, near 80Gb) I came up with these steps to speed up things and keep transaction log small:

Here is a sample of batch processing for an update, 1000 rows at a time:

 UPDATE TOP(1000) your_table
 SET    col1 = new_value
 WHERE  <your_condition>
 WHILE  @@rowcount > 0
     UPDATE TOP(1000) your_table
     SET    col1 = new_value
     WHERE  <your_condition>;
  • restore nonclustered indexes




Basically you're option is to batch the updates into smaller chunks of 1000 or 10000 rows so that you don't have one massive transaction. If there's an ID column or a date column you can use this becomes easy, if not it's a little trickier but still doable.

You'll be using update with the top limiter and running the statement multiple times until you get all the rows updated.

  • Good points - clarified question to include ID column consideration Commented Mar 12, 2014 at 17:37
  • 1
    When writing the update just start with the lowest ID value and update 1000 values, reset your variables for the next 1000 and update again. Repeat until you run out if rows to process.
    – mrdenny
    Commented Mar 12, 2014 at 17:40
  • 1
    I'd like to add to @mrdenny's answer, to wrap the update statement into TRY ... CATCH with an explicit transaction. Also truncate the transaction log after each iteration with a backup of the log if in FULL, with CHECKPOINT if in SIMPLE -- "to reduce the peak disk space consuption" of the log file.
    – DenisT
    Commented Mar 12, 2014 at 17:57

If you haven't got a clustered index (theoretically because you have one): Please pay attention what you update on the column. If you increase the record size you might end with a lot of forwarded records (see dm_db_index_physical_stats - forwarded_record_count).

But also with a clustered index you should pay attention to the updated row size. It might lead to page splits and fragmentation. If you've got a scheduled maintenance it could lead to a delayed increased transaction log size.

If it's possible change the recovery model to simple. No matter how much records you're modifying in total: you only need the batch chunk size as transaction log. Make sure you can restart the whole job and only untouched rows are updated and not every row from the start!

If you had an index on this column you should disable it for the update and rebuild it afterwards.

You might want to check about statistics on these column - but that's a performance question which you don't asked.

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.