is it ok to use identity function to the large table?

Question

is it ok to use identity function to the large table?

Haryadi Santoso

Old Hand

Points: 334
More actions
August 27, 2006 at 9:10 pm

#170001

Dear All,
Please advise and solution from u all, regarding this matter:
I have a table about Car Service whose total data more than 2,5 million. Then I re-create the table with added column (RecordID) like this:
select
identity(int,1,1) as RecordID,
a.*
into Car_Service_Ordered
from Car_Service a
order by a.VIN, a.ServiceDate
the problem is:
sql server gives me a message
'Server: Msg 9002, Level 17, State 2, Line 1
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.'
when i run the sql
'select top 10000 * from Car_service_ordered order by recordid'
instead, when i run the same sql to the table Car_Service, it is running fine...
is there anyone has the same experience about this?
how to solve the error messages?
is it because i used identity function to a large table?
thx in advance all....waiting 4 ur reply..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Haryadi Santoso Old Hand Points: 334 More actions · Answer 1

fyi, i have shrunk and truncated the log file for tempdb database...but the problem still can't be solved...and the server harddisk still has 45 GB free capacity...

Jo Pattyn SSC-Dedicated Points: 32344 More actions · Answer 2

Looks your tempdb is running full or it can't grow fast enough.

Can you split your command in batches of 100 000? With a counter or something to remember the highest number?

Haryadi Santoso Old Hand Points: 334 More actions · Answer 3

Dear Jo,

Why tempdb is faster to become full when i run such query in the table which has an identity column (Car_Service_Ordered)?

Why it doesn't happen in the same table (Car_Service)? whereas this table doesn't have an identity column.

Thx in advance for the explanation

Jo Pattyn SSC-Dedicated Points: 32344 More actions · Answer 4

In such situations it is always helpfull to compare the execution plans of the queries, you can do this via the query analyzer: (Query) show estimated execution plan.

Possible reasons: the identity field is an extra 4 bytes. You also sort different vin,service date vs record id.

You could change your select into statement into a seperate create table + insert statement because select into can lock some systemtables. A good idea is to use column names instead of * to avoid trouble if the order of the columns would ever change.