Re: The newbie rolls along

Thanks Mark

Can I assume these calls don't require a database restart to recognize the
changes?

"Mark A. Parsons" <user@xxxxxxxxxxx> wrote in message
news:F%8bg.173919$7a.68579@xxxxxxxxxxx
To 'grow' the transaction log you have a couple options:

1 - add a new chunk of disk space to the database (alter database) as
log-only space; problem with this is that, officially, you cannot shrink a
database (ie, you can't drop that chunk of disk from the database after
you're done with it)

2 - use 'sp_extendsegment' to grow the logsegment onto a disk fragment
currently used for data (assumes you actually have a data segment with
some
extra/free room), do your data modification thang, then run
'sp_dropsegment' to remove the logsegment from that disk fragment; the log
will continue to use space on that disk fragment until a) the log has
moved
back onto one of the old fragments and b) the log has been
truncated/cleared from that temporary disk fragment

GWood wrote:

Well, for two weeks of intense self-education, the progress has been
pretty
good. The code I am writing spins through all tables in all databases
in
our particular server, and seems to generate and execute DML correctly
on
the desired tables. The version is 11.9.2

The current problem is one of transaction logs. There are about 70
databases in this installation (vendor package), and each database seems
to
have a different sized transation log. My code is able to process table
rows in chunks by checking a "master" table, but some of the database
tables
have large numbers of associated rows even with the most restrictive
criteria. The transaction logs in some of these databases seems tiny!

To help understand the issue, consider that I have a KEYLIST table that
contains the key values for all the rows to be removed from an INVOICE
table
(names are for illustration only). The KEYLIST table has a row
identifier on
it, so I can process the INVOICE table in a loop, such as
Loop

Delete from INVOICE where EXISTS

( select KEYLIST.keyval

FROM KEYLIST where KEYLIST.keyval = INVOICE.keyval

and KEYLIST row_identifier between n1 and n2)

Adjust n1 and n2

end Loop

I am adjusting n1 and n2 within reason to try to avoid busting the log
files. I keep the span as large as possible since there are 80K+ rows
in
KEYLIST, and I have a limited time window for this code to execute (data
conversion schedule - sigh).

I have found one "INVOICE" table that will impact 90K rows when n2 is
n1+10.
So I'm getting close to having to process row by row. With the time
factor,
I'm looking for alternatives.

So, my question is: Can I programatically adjust the log file size as I
start to process in each database? I would bump the log size up for the
duraction of my processing, then restore it after the final commit for
that
database.

Any ideas appreciated.
Gary




.

Relevant Pages

  • Re: Database Copy
    ... If you use all chunk on block file system(not using raw device), ... umount old disk. ... >We have a 350Gb database running baan that the customer wishes to ...
    (comp.databases.informix)
  • Re: High Avg. Disk Queue Length When Opening Shared Calendars
    ... Let's assume 10K spindles, a 3:1 read/write ratio, and IOPS/user of 1. ... Now let's look at the logs. ... the database LUN would become a bottleneck prior to the log LUN. ... A disk bottleneck on the database LUN. ...
    (microsoft.public.exchange.admin)
  • Re: Oracle Performance -- Possible Disk Bottleneck
    ... I've read that 15k drives can perform 180 IO's per second. ... confirm this is a disk bottle neck. ... so I'm hoping someone here has experience with SANs and ORACLE to help ... this is a disk bottleneck and that giving the database more spindles ...
    (comp.databases.oracle.server)
  • Re: Chaotic IMAP Message list
    ... bits of the database which are scattered about on your hard disk into a new ... break at an unfortunate location in the database. ... I was busy and didnšt read carefully so I thought you were advocating the more complete rebuild solution. ... or can you offer a thumbnail explanation of what happened and why a compact would fix it? ...
    (microsoft.public.mac.office.entourage)
  • Re: Oracle Performance -- Possible Disk Bottleneck
    ... confirm this is a disk bottle neck. ... ton of information in the statspack report, so I'm not sure what else ... so I'm hoping someone here has experience with SANs and ORACLE to help ... this is a disk bottleneck and that giving the database more spindles ...
    (comp.databases.oracle.server)