5

This is a legacy system, with more than 1 billion records in a table that undergoes 10 million transactions per day.

Now the application complains that:

Arithmetic overflow error converting IDENTITY to data type int

We want to change that id column's data type to bigint, but it can't do it and times out.

What should we do? I have no clue at all. We can't stop the system, because it's a monolithic application and database and does many things. Thus we prefer not to stop the whole system.

We're using SQL Server 10.50.6000.34, that is SQL Server 2008 R2 SP3 (September 2014)

  • 1
    Regarding your edit instead of just trying stuff randomly you should investigate why renaming the table took 4 hours. Presumably it was waiting on a lock so you should investigate why it was apparently unable to get one for 4 hours and what is blocking it and can it be killed. It may be a single long running tran or it may just be many concurrent transactions trying to access the table. These are supposed to queue up to avoid starving the session waiting for the SCH-M lock but IIRC there was a bug in some versions where this didn't happen. – Martin Smith Sep 3 '16 at 19:49
  • 1
    I imagine that in this 4 hour window you likely could have just fixed it properly though. – Martin Smith Sep 3 '16 at 20:02
7

Assuming your current table is something like

CREATE TABLE YourTable
  (
     Id           INT IDENTITY PRIMARY KEY,
     OtherColumns VARCHAR(10)
  )

And that your existing code that references the table just performs basic DQL and DML commands against the table (i.e. SELECT/UPDATE,MERGE,INSERT,DELETE)

And that YourTable(Id) isn't referenced by a foreign key anywhere.

Then possibly the best way of getting up and running quickly (if you can't afford the downtime of rebuilding the whole table in one go) would be to rename that table (e.g. as YourTableInt) and create a new table

CREATE TABLE YourTableBigInt
  (
     Id           BIGINT IDENTITY(2147483648, 1) PRIMARY KEY,
     OtherColumns VARCHAR(10)
  )

You could then create a view with the same name as your original table name.

CREATE VIEW YourTable
AS
  SELECT Id,
         OtherColumns
  FROM   YourTableInt
  UNION ALL
  SELECT Id,
         OtherColumns
  FROM   YourTableBigInt 

You would need to write INSTEAD OF triggers that route the Inserts/Updates/Deletes to the appropriate table. This could initially be based on whether Id was <= 2147483647 or not but that isn't going to work if you try and migrate rows in the background from the legacy table to the new one so probably best to do the following.

Delete trigger Apply deletes against both tables by joining on id

Update trigger Apply updates to both tables by joining on id

Insert trigger Route all inserts into the new "YourTableBigInt" table. It shouldn't be possible for an insert through the view to enter an explicit identity that might clash with anything in the original table as any attempt to set identity_insert YourTable will fail now that is actually a view.

You could then have a background process that deletes batches of rows from YourTableInt and outputs them into YourTableBigInt. Once the original table is empty you can drop it and the view and rename YourTableBigInt to YourTable.

5

As an interim solution since you said you can't shut down the system, at least maybe for a short time...

Remember that the data type is a signed INT (pos2bil to neg2bil), meaning there are 4bil records allowed, not just 2bil. So, why not consider doing a DBCC CHECKIDENT to change the identity to increment from negative nbrs, and let it go for another 2bil records until you figure out what to do. That should be a quick fix.

Hopefully, you shouldn't be using the IDENT for any business purpose other than just FK, like order by, to care about the order of the records on disk if IDENT is CLUST.

So careful about certain considerations of use no negative nbrs.... But you're in a hard situation that this may work for you, so I'm just providing an idea for a way out...

This is one of the things I don't like about signed data types for IDENT, "loss" of 2bil entries.

0

You can use this technique, first create new table as following with the conversion of datatype by copying first table structure:

SELECT id, CAST(test_id AS BIGINT) test_id INTO test_new  FROM test

Then verify the data in new table and remove old one and again rename new table name as old one.

  • 1
    Is this efficient? With over a billion records? – Saeed Neamati Sep 3 '16 at 7:24
  • Definitely, if you are getting time out issue due to huge data, I had done it in up gradation time and it was very helpful to me while operating with 400GB's one table. – Susang Sep 3 '16 at 7:27
  • You mean using into clause, I copy data and schema from old database, and at the same time cast int to bigint into a new table, and then after verifying that the operation is done, remove the old table and rename the new one the the old name? Doesn't that freeze the database? It's under a heavy load of transactions. I'm talking about database locks because of schema changes. – Saeed Neamati Sep 3 '16 at 7:33
  • It would better to not to perform such an operation on application running state so you have to manage it in the off hour if any applicable and try to perform simple ALTER command first. – Susang Sep 3 '16 at 8:09
  • 1
    There is no way i would use select into on a 1 billion row table in production. It would fill up the transaction logs and system would grind to a halt. But you could insert into the new table in blocks. Say 1 million rows at a time. – Sir Swears-a-lot Sep 3 '16 at 8:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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