July 18, 2010 at 6:57 am
I have a really wide table with 5.4 million rows.
I had to transfer that into another table with the exact same schema.
My problem is that I forgot to turn on identity for my pkey field. After I filled the table I then tried turning on identity but I guess that must log a large amount of transactions. Because when I turned it on, it filled up my log space (and my data space)...I can't add any more room to this server..Plus it took forever..
Is there any way I can turn on identity for my pkey field without generating a huge log/data space?
July 18, 2010 at 9:48 pm
Can you reload the data? That might be the simplest way to go..
What recovery mode are you in? Perhaps you can switch it across to simple or bulk_logged while making the change?
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 21, 2010 at 11:52 am
If you are trying to make an existing column an identity field, the table has to be rebuilt (in other words, you essentially have to copy the data into a brand new table); and with a wide table, 5.4M rows, and limited space, I can see that causing problems. Unfortunately, a simple "alter column" statement will not work.
If you cannot reload the data as torpkev suggested, maybe you can add a trigger to the table that auto-increments the field? If the pkey field is clustered, and you only have 5.4 million rows, performance should not take too much of a hit.
Other than that, you really can't make the field an identity column without rebuilding the table.
krypto69 (7/18/2010)
I have a really wide table with 5.4 million rows.I had to transfer that into another table with the exact same schema.
My problem is that I forgot to turn on identity for my pkey field. After I filled the table I then tried turning on identity but I guess that must log a large amount of transactions. Because when I turned it on, it filled up my log space (and my data space)...I can't add any more room to this server..Plus it took forever..
Is there any way I can turn on identity for my pkey field without generating a huge log/data space?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply