change DATA TYPE from huge tables

[salvatore]

i have huge tables: e.g. 770000 records, 5 rows.
when i now try to chnage the data type of a row and save, an error occure: "microsoft access can´t change the data type There is not enoug discspace or memory."
i am sure it is not the discspace or the memory. it is just the sice of the table. the data base is in access 2000 file format.
any idee how i can propper ship around this problem whit out spliting the table?

 

[neileg]

First, I would try a Compact & Repair. If that works, try your change again. If it doesn't, then you may well be short of disk space. Remember that for a change such as you suggest, Access has to hold both the old and the new version at the same time.

 

[The_Doc_Man]

Actually, it MIGHT be memory. VIRTUAL memory.

I would do some things before attempting this change again.

1. Defrag the disk.
2. Check my system's swap file allocation and allow it to get larger than my DB (at LEAST 50% larger if not 100%).
3. Repair and then compress the DB.
4. Defrag the disk a second time. (Should go MUCH faster.)
5. NOW try the data change.

Why? 'cause of the way Windows works. When you change the data type on a table, what it does is replicate the records, changing the formats one record at a time. Then it marks the old records for deletion. BUT the old records cannot go away yet. That's why step #3 is needed.

The other stuff is because as the duplicate records are created, the DB has to change size, but the only way this can happen (given the memory structures Windows uses) is to outswap, muck the memory allocation, and inswap larger. This will happen a lot, so you will need a swap space at least as big as the sum of the virtual memory of your DB + plus the virtual memory of everything else that gets outswapped when your DB is about to be inswapped. So step #2 is required to assure proper swap space is allowed.

And finally, even if the swap file is allowed to get big enough, if there are too many files in the way, the swap file won't grow 'cause there won't be enough contiguous space available. So steps #1 & #4 are necessary to assure that your disk's free space is as big as you can get it.

After a repair/compress, you will create "life-sized" copies of the old DBs that get deleted when the repair is done and when the compress is done. So there will be more fragmentation after each of those steps and you must take great pains to remove the problem before attempting any crucial steps. Which is why you defrag TWICE.

 

[salvatore]

tanx for the fast response.
my access db is 142MB.
my virtual memory is setted on 756MB.
ram is 516MB.
the c drive partition is 20GB; 15GB of it are free.
unfortunitely i have at the moment no right to defrag the comuter.
but the same problem occures aswell on an older machine.
just repari & compress bing no success...
do you really think it is a memory problem?

 

[The_Doc_Man]

do you really think it is a memory problem?

The error you quoted is pretty much unequivocal. Access thought it was out of memory. The only ways that can happen are that you are out of physical memory or you are out of virtual memory. The errors for "out of resources" is a different issue.

If this is a stand-alone DB OR you can MAKE it stand-alone for a while, you might try another approach.

Export the table to text using a comma-delimited format (or some other character that doesn't occur in your data, like | or ~ or ^). Export the table perhaps using a query that formats the output of your changing field to be consistent with the new format. ALSO export the table sorted according to the prime key of the table. (Don't trust this to luck; explicitly select a SORT order.)

Create a new table with the correct data type for each field (the type you wanted, not the type you had before.) This table will be empty. Define the prime key.

OK, now import the text file to your DB using the "import to existing table" option. This doesn't involve as many intermediate entities inside the DB, so it minimizes the growth of the DB.

If THIS fails, something else you said becomes significant. If you don't have the rights to do a DEFRAG on your assigned computer, the question arises as to what ELSE you are not allowed to do. It sounds as though your domain security folks have imposed a restricted environment on you.

If so, your next question is to the domain security guy to find out if they have forced a profile on you that limits virtual image size. This is entirely possible. If the answer is "yes" and the limit is something like 256 MB, there's your answer. It is not unusual for the data change you described to grow a DB by 100% ('cause from the sound of it, your big table is most if not ALL of your DB). And twice 142 is bigger than 256.

If THIS is your problem, you might need to export ALL tables from the DB, do the data changes in a NEW db, and re-import the tables. But before I would go that far, I'd talk to my domain security manager. And to my boss if the domain guy won't budge. 'cause you have a case where your security is now standing in the way of doing a legit and documentable job. (It's not like you were importing and embedding porn pix in your DB, after all...)

 

[neileg]

DocMan, it the PC is a standard installation of NT, W2k or WXP, there's no defrag options. The idea is that NTFS partitions never need defragging...

 

[The_Doc_Man]

Neil, you are right (that was the idea) but whoever had the idea was wrong.

Swap files are special. They cannot be fragmented so badly that they have more than one set of extent pointers. (I.e. no extension headers.) Which is not the case on a heavily used system. And, for what it's worth, you DO INDEED have defrag options.

You are right that WinNT doesn't have DEFRAG. For Win2K & WinXP, DEFRAG is found from

Start >> Programs >> Accessories >> System Tools >> Defrag

If you don't have them on your work systems, your security manager has been mucking about in YOUR system, too.

Trust me, the Executive Software company wouldn't make money off of the Diskeeper program on NTFS systems if it had no function and no value. And if you look at the DEFRAG in XP Home, you would realize that the copy of DEFRAG you have was MADE by Executive Software and is licensed to MS by them. Why would MS license ANYTHING that they thought had no value.

DEFRAG is an incredibly valuable tool because it controls overhead on really big files. Every fragment (extent) of a file is an extra I/O operation when you reach the end of the fragment. Even for a fast disk, you run into issues of head latency when fragmentation gets bad enough. And for a swap file, fragmentation kills performance AND kills the ability of Windows to respond to unusual swapping requirements.

 

[Pat Hartman]

I don't think you need to export the data. I think you can just run an append query to take it from one table to another.

When you change the data type, Access needs to hold everything in memory while it runs through all the rows just incase there are errors and you need to back out of the change. You won't run into this problem with the append query. Rows with invalid data will not be appended.

 

[neileg]

Neil, you are right (that was the idea) but whoever had the idea was wrong.

Yeah, I know. I've used Diskeeper myself. That's why I used the elipsis (dot dot dot) on the end of my post. Perhaps I was a bit too subtle!

 

[salvatore]

i check it like you told whit defrag.
no success!
just to get sure:
-whit "my system's swap file allocation" you mean the "paging file sice of the virtual memory"?
-where can they domain-security-guy force the virtual-image-size of my profile?

 

[The_Doc_Man]

To Neileg -

Perhaps I was a bit too subtle!

Naw, I'm too dense.

To salvatore -

whit "my system's swap file allocation" you mean the "paging file sice of the virtual memory"?

Sounds right. The name of that quantity apparently changes from version to version, but that's as good a description as any.

where can they domain-security-guy force the virtual-image-size of my profile

In something called the "user policy" - which is something that your domain architecture automatically downloads and applies from your domain controller each time you log in. Watch the bottom of your Windows logo (if on Win2k) after a network login. You'll see "loading user settings" - which you cannot prevent from running. That's why I suggested that you ask the domain security guy to see if they ever set that. (They don't have to.) If it is set by domain-based policy, you cannot override it unless you have Admin rights on your assigned PC - and your comment about not having the right to defrag the computer made me think you must not be an admin person.

I'm not saying this is a likely setting to use, but WinNT and later have more settings than a mad scientist's console from a bad sci-fi movie. (Come to think of it, the comparison is more apt than I intended. Windows is very much like a bad sci-fi movie...)

 

[gemma-the-husky]

Can you create a new column of the data type you want, and massage the old data into the new column.

Then rename the columns so that the new column is named the as the previous column.

I am not sure offhand what problems this might cause with existing indexes.

What are the data types you want to change?