Unable to compile table (AutoIncrement)

Eboy82

Hi,

I have run into an issue on a table on which I would like to enable auto-increment on it's primary key - which is a integer.
I can do it easily on my own test installation on the table that has like 10000 rows - including a blob field.
However, if I try to attempt it on a copy of a production database with 300 K rows - the development environment hangs on compiling the object, and the SQL logfile size goes nuts and eventually would crash the database server.

Any idea how to solve this?

Running NAV 2017 btw.

Juhl

If records exist it will generate Numbers for all records, and if one record has 0 it will fail. If you at the same time make it PK, it can't think of the complications you might meet.
Maybe use upgrade codeunit and move all records to upgrade table and insert them back in to the changed table.

Juhl

If records exist it will generate Numbers for all records, and if one record has 0 it will fail. If you at the same time make it PK, it can't think of the complications you might meet.
Maybe use upgrade codeunit and move all records to upgrade table and insert them back in to the changed table.

Eboy82

Thanks for suggestion. I guess that would sort it - I will keep in mind, but I would need a lot of free space to do this.

On my test database that already has 10.000 rows - after enabling AutoIncrement, the field ("Entry No.") was not re-generated. I did notice that my test machine also uses quite some disk space to do this operation - so I guess this is could be my issue as SQL server where it fails only has like 30 GB free on log drive.

Would be interesting to know what happens in this process.
Perhaps it's considering renaming on all records or something?
The table is primary key "Entry No." of type integer, not to be changed.

kennie

Which version of SQL Server are you running?

What is in the SQL Server error log before it crashes?

Are you sure that it is a constraint on the log drive? I would suspect that a lot of tempdb activity could occur

Eboy82

Hi,

Running SQL 2014 . At such it doesn't crash as to what I can see, I can just see that the drive goes from having 30GB free to 10 MB free, and then I get nervous!

At that point, I can't even browse tables / views or take properties on the Database in Config. Studio. What I have done is then close the development environment that still hangs, close the NAV server tier, and restart the SQL service tier.

Then it would go for recovery process for the database and after like 0.5-1 hours it would report no errors in windows log for the recovery process, and database works fine again. If I tried to change the compile the table again - same would repeat it self.

The log drive actually is the same as data drive in this case. So maybe you right about this tempDB, how to check.

When I did the attempt to compile the large table the first time, I noticed the ndf also file grew with 20-30 GB too for no reason, but that was only the first time. It is stuck on 170 GB at the moment - I tried to compile the table like 3 times, that table alone is like 90GB

The hosting people have increased the free space on the drives from 30 to 300 GB now, but I am scared to attempt this operation again