June 25, 2004 at 8:39 am
If you have a table with 500.000 records and you add 250.000 and delete 250.000, all in one transaction, how big will the transaction log become?
Is there a formula about the transaction log size e.g.
log size = a (number of records involved) * recordsize + b?
= a * 500.000 * recordsize + b.
Or is the only way to find the answer to try it?
Thanks in advance.
June 28, 2004 at 7:09 am
depends on the table design, but as a rule i've always found that
(number of bytes per row)*10*(qty rows) gives a reasonable interpretation of what you might get.
it also depends if you have automatic checkpointing working, how you add or delete the rows etc. etc etc.
MVDBA
June 28, 2004 at 7:47 am
Thanks,
By trying
begin transaction
delete from xxxx
where xxxx_id>270000
insert into xxxx
select * from xxxx2
where xxxx_id > 270000 and xxxx_id < 540000
commit transaction
the logfile grows till 604 MB,
while the table xxxxxx with 540000 records has a size of 80 MB. The logfile becomes bigger than the data which will be inserted.
June 28, 2004 at 9:47 pm
That's very possible. What kind of indexes do you have on the table?
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
June 29, 2004 at 12:42 am
With only a primairy key it grows to 150MB, when you add a unique constraint on 7 fields it grows to 604 MB: the indexes do matter.
But during the transaction the log grows to 604 MB, after that only 150 Mb remains:is the log compressed after the transaction or is data removed from the log?
June 29, 2004 at 6:16 am
is your database in simple mode? the database is proboably performing a checkpoint.
MVDBA
June 29, 2004 at 6:39 am
I've tried again, but the logfile starts at 10MB in use, grows to 504MB and after the transaction is finished is returns to 257 Mb.
But the recovery model is Full of the database.
June 29, 2004 at 7:13 am
When you delete 540000 records and add 540000 records the logfile grows to 1054MB but when the transaction is finished only 22,9MB of the logfile is uses and 1032MB is free. The recovery model is still Full. I would see that the log file is compressed after a successfull transaction.
begin transaction
delete from xxxxxx
where xxxxxx_id<540000
insert into xxxxxx
select * from xxxxxx2
where xxxxx_id<540000
commit transaction
(539997 row(s) affected)
(539999 row(s) affected)
July 2, 2004 at 5:14 am
If you are backing up/archiving the transaction logs at frequent interval, say every 15-20 minutes, the onlone transaction log size would be kept small. I'm assuming that you have plenty space for storing the archived logs. I think it's important to keep online transaction log(s) small. A huge log would take very long time to recover when you need to restart the database, not to mention the loss you may suffer should it become corrupt.
July 2, 2004 at 5:26 am
The size of one transaction is 620 MB at the end of the transaction. It is strange to me that after the transaction the transaction log shrinks with a full recovery model.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply