Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I'm going to guess that you are closing in on the 2.1billion limit of an INT datatype on an artificial key for a column. Yes, that's a pain. Much easier to fix before the fact than after you've actually hit that limit and production is shut down while you are trying to fix it :)</p> <p>Anyway, several of the ideas here will work. Let's talk about speed, efficiency, indexes, and log size, though.</p> <h2>Log Growth</h2> <p>The log blew up originally because it was trying to commit all 2b rows at once. The suggestions in other posts for "chunking it up" will work, <em>but</em> that may not totally resolve the log issue.</p> <p>If the database is in SIMPLE mode, you'll be fine (the log will re-use itself after each batch). If the database is in FULL or BULK_LOGGED recovery mode, you'll have to run log backups frequently during the running of your operation so that SQL can re-use the log space. This might mean increasing the frequency of the backups during this time, or just monitoring the log usage while running.</p> <h2>Indexes and Speed</h2> <p>ALL of the <code>where bigid is null</code> answers will slow down as the table is populated, because there is (presumably) no index on the new BIGID field. You could, (of course) just add an index on BIGID, but I'm not convinced that is the right answer.</p> <p>The key (pun intended) is my assumption that the original ID field is probably the primary key, or the clustered index, or both. In that case, lets take advantage of that fact, and do a variation of Jess' idea:</p> <pre><code>set @counter = 1 while @counter &lt; 2000000000 --or whatever begin update test_table set bigid = id where id between @counter and (@counter + 499999) --BETWEEN is inclusive set @counter = @counter + 500000 end </code></pre> <p>This should be extremely fast, because of the existing indexes on ID. </p> <p>The ISNULL check really wasn't necessary anyway, neither is my (-1) on the interval. If we duplicate some rows between calls, that's not a big deal.</p>
    singulars
    1. This table or related slice is empty.
    1. POCopy one column to another for over a billion rows in SQL Server database
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USBradC
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCopy one column to another for over a billion rows in SQL Server database
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COI kicked off a script similar to yours with some modification as i have ids from -2B to +2B. My expectation was that the transaction log will not grow by large scale as I am updating 500K rows max in a batch. The transaction log is set up to grow incrementally by 10G upto 100G. The log just hit max and I see the query rolling back. I even put a begin tran and commit around the update statement to make sure that the transaction is incremental. Or could that begin tran and commit in loop might be causing the transaction log to grow?
      singulars
      1. PO
      1. USAdi Pandit
    2. COlooks like the begin tran and commit that I had added were causing the transaction log filling up. Removed those from my script and the procedure has been ripping through the rows crisply overnight without filling up the transaction log :-)
      singulars
      1. PO
      1. USAdi Pandit
    3. CO@Adi: As long as you have the right syntax (`BEGIN TRAN` and `COMMIT TRAN`), then putting those on the *inner* loop should be the same as my script (SQL uses implicit transactions by default, which is why batching like this works). I once put just `COMMIT` instead of `COMMIT TRAN` and ended up with crazy nested transactions that blew up the log like you said. Just don't put the transaction around the *outside* of the loop.
      singulars
      1. PO
      1. USBradC
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload