Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

3 Answers 3

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.

share|improve this answer
    
Good points - clarified question to include ID column consideration –  BrianCooksey Mar 12 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 Mar 12 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 Mar 12 at 17:57

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
 BEGIN
     UPDATE TOP(1000) your_table
     SET    col1 = new_value
     WHERE  <your_condition>;
 END;
 GO
  • restore nonclustered indexes

  • SET TRANSACTION ISOLATION LEVEL READ COMMITTED

  • ALTER DATABASE [my_db] SET RECOVERY FULL;

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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