Re: The newbie rolls along
- From: "Mark A. Parsons" <user@xxxxxxxxxxx>
- Date: Fri, 19 May 2006 01:16:21 GMT
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
- Follow-Ups:
- Re: The newbie rolls along
- From: GWood
- Re: The newbie rolls along
- References:
- The newbie rolls along
- From: GWood
- The newbie rolls along
- Prev by Date: The newbie rolls along
- Next by Date: Sybase API call to get OS information
- Previous by thread: The newbie rolls along
- Next by thread: Re: The newbie rolls along
- Index(es):
Relevant Pages
|
|
