Re: [Info-ingres] Very Large Table and Update

<martin.bowes@xxxxxxxxxxxxx> wrote in message
news:mailman.1153405202.9853.info-ingres@xxxxxxxxxxxxxxxxxx
Hi Everyone,

I have a very large table (15G) that I need to update. This blows my 1G
Transaction Log file out of the water.

Although I could in this particular case break the single update into lots
of separatly committed transactions, I'm not happy with this as a
general solution. The thought of something failing halfway through and
then leaving me with the mess of working out what commited - what
didn't and how to restore the situation just gives me the creeps.

Set nologging similarly sucks.

Has anyone got a better solution? And No I don't want to have to make
my Log file > 15G.

I see that a number of people have already proposed the suggestions I would
have made first.

If this is a one-off task and not something you need to be able to do
routinely, consider using CREATE...AS SELECT to create a new table with the
update being done on-the-fly. Provided the new table is not created with
journaling (or anything else that would defeat a bulk-load), and you have
the necessary 15Gb, it should do virtually no logging. What you do after
that is a bit messy--I'd probably be happy to drop the original table and
create a synonym for the new one. (BTW, a forthcoming release of Ingres is
probably going to support renaming of database objects.) If you don't like
that, take a bit longer and repeat the same trick again to create a new copy
of the new table, with same name as the original table had.

Roy


.

Relevant Pages

  • [Info-ingres] Very Large Table and Update
    ... Transaction Log file out of the water. ... general solution. ... Random Duckman Quote #44: ...
    (comp.databases.ingres)
  • Re: Big transaction log file
    ... SIMPLE recovery will remove committed transactions from the log up to the oldest uncommitted transaction. ... But be aware that a truncate does not release log file space back to the OS; truncate just marks the space is as available. ... When I do "DBCC SQLPERF" it shows that very little of the transaction log file is being used: ... Don't the full backup on a Simple database usually truncate the transaction log file? ...
    (microsoft.public.sqlserver.server)
  • Re: Rolling forward through all the log files
    ... Transaction log file Exchange 5.5 <Transaction log file Exchange ... >Also how do you stop the connection with that server with the others so we won't go through this round robin effect. ...
    (microsoft.public.exchange2000.misc)
  • Re: Error: Cannot create new transaction because capacity was exceeded
    ... >>I checked the transaction log file size, it had about 53 MB of free space ... >>and no limitation on growth. ... >> Martin. ... >>> Maybe a file size limitation on the Log file? ...
    (microsoft.public.access.adp.sqlserver)