transaction log 
Author Message
 transaction log

Hi.
I am currently trying to test a threshold that
dumps the transaction log when it fills up to 50% of
its size.

In order to do that i'd need to fill the transaction log quickly,
are there any tricks to perform that (instead of doing 10000 inserts
or updates)?

THanks ,
eRwan Pianezza, Galway, Ireland



Mon, 24 Apr 2000 03:00:00 GMT
 transaction log

Hi,

I know it's doing inserts and deletes but I have used the following to
set the desired result

isql -Usa -Sserver -Pwhatever
1> use db
2> go
3> begin tran
4> create table tab(various fixed length largish char fields)
5> insert tab values (appropiate values)
6> delete tab
7> rollback tran
8> go 10000

This normally does the trick!

Quote:

> Hi.
> I am currently trying to test a threshold that
> dumps the transaction log when it fills up to 50% of
> its size.

> In order to do that i'd need to fill the transaction log quickly,
> are there any tricks to perform that (instead of doing 10000 inserts
> or updates)?

> THanks ,
> eRwan Pianezza, Galway, Ireland

--
Toby Arkless
Wilco International Ltd
Remove the XX to reply by email


Mon, 24 Apr 2000 03:00:00 GMT
 transaction log

Quote:
> > Hi.
> > I am currently trying to test a threshold that
> > dumps the transaction log when it fills up to 50% of
> > its size.

> > In order to do that i'd need to fill the transaction log quickly,
> > are there any tricks to perform that (instead of doing 10000 inserts

> > or updates)?

> > THanks ,
> > eRwan Pianezza, Galway, Ireland

I'm curious - how do you set a threshold for automatically dumping a
transaction log when it fills up to 50%?  Right now we have a process
that "wakes" up twice a day to dump the transaction log.  This approach
works 90% of the time - i.e., the logs aren't full.  However, the other
10% of the time the logs fill up before there dumped and the end users
start screaming.

Opinions expressed herein are my own and may not represent those of my employer.



Mon, 24 Apr 2000 03:00:00 GMT
 transaction log



Quote:
>> > Hi.
>> > I am currently trying to test a threshold that
>> > dumps the transaction log when it fills up to 50% of
>> > its size.

>> > In order to do that i'd need to fill the transaction log quickly,
>> > are there any tricks to perform that (instead of doing 10000 inserts

>> > or updates)?

>> > THanks ,
>> > eRwan Pianezza, Galway, Ireland

>I'm curious - how do you set a threshold for automatically dumping a
>transaction log when it fills up to 50%?  Right now we have a process
>that "wakes" up twice a day to dump the transaction log.  This approach
>works 90% of the time - i.e., the logs aren't full.  However, the other
>10% of the time the logs fill up before there dumped and the end users
>start screaming.

You set threshold actions up on a DB as a stored procedure that gets called
when percentage thresholds are reached. The SQL Server System Admin Manual
describes these techniques in detail. They work far better than cron jobs
or home-brew daemon processes, because once introduced, SQL Server will
automatically fire them as they are needed.

Bob McIlree



Tue, 25 Apr 2000 03:00:00 GMT
 transaction log



Quote:
>Hi.
>I am currently trying to test a threshold that
>dumps the transaction log when it fills up to 50% of
>its size.

>In order to do that i'd need to fill the transaction log quickly,
>are there any tricks to perform that (instead of doing 10000 inserts
>or updates)?

>THanks ,
>eRwan Pianezza, Galway, Ireland

There isn't any way to do that without performing transactions,
but you could test your dump threshold on a modestly sized DB
and log, like a 20 MB DB with a 5 MB log. At those small sizes,
you'll get your threshold results pretty quickly - in a few
minutes. More so than trying it on a VLDB.

Bob McIlree



Tue, 25 Apr 2000 03:00:00 GMT
 transaction log

Quote:

> I am currently trying to test a threshold that
> dumps the transaction log when it fills up to 50% of
> its size.

        Er, 50% is a tad excessive don't you think?
        Barring open transactions, this means in theory
        that you're wasting the rest of your log.

-am



Tue, 25 Apr 2000 03:00:00 GMT
 transaction log

Quote:


> > I am currently trying to test a threshold that
> > dumps the transaction log when it fills up to 50% of
> > its size.

>         Er, 50% is a tad excessive don't you think?
>         Barring open transactions, this means in theory
>         that you're wasting the rest of your log.

> -am

50% isn't excessive depending on the size of your logs and what you have
set for your database options. ie: Do you abort transactions or suspend
transactions when the logs fill up. Once the threshold fires it still
takes time to actually dump the log rows to tape or whatever you want to
do.  If you set your threshold to say 90% and you have very large logs
and a high number of transactions being done,  you can potentially fill
up the logs and have queries aborting or suspending,  before your
threshold process has finished. Keep in mind that just because your
threshold fires, things don't stop processing. This has happened to us
and we have lowered the threshold to 75%.


Tue, 25 Apr 2000 03:00:00 GMT
 transaction log

Quote:

> 50% isn't excessive depending on the size of your logs and what you have
> set for your database options. ie: Do you abort transactions or suspend
> transactions when the logs fill up. Once the threshold fires it still
> takes time to actually dump the log rows to tape or whatever you want to
> do.  If you set your threshold to say 90% and you have very large logs
> and a high number of transactions being done,  you can potentially fill
> up the logs and have queries aborting or suspending,  before your
> threshold process has finished. Keep in mind that just because your
> threshold fires, things don't stop processing. This has happened to us
> and we have lowered the threshold to 75%.

        Yes, it depends on the size of the log, the expected insertion
        rate and the time to dump. For a threshold action I would
        recommend dumping to disk rather than tape. Not only is it
        faster, but it makes more sense logically. You've crossed a
        threshold that you ordinarilly don't expect to, so your dumps
        should be fast to accommodate the unexpected (I hope some
        don't use the threshold as a normal procedure, I don't
        think its meant to be use like that). They way I've designed
        my logs is based on their size on a busy day. I make the
        log a percentage larger than this (to accommodate future
        growth etc.) and then set the threshold to be within this
        extended region. I've rarely seen the threshold crossed.
        But the logs aren't overly large, nor are they sized based
        on the size of the data segment(s).

-am



Wed, 26 Apr 2000 03:00:00 GMT
 transaction log



Quote:




>> >Hi.
>> >I am currently trying to test a threshold that
>> >dumps the transaction log when it fills up to 50% of
>> >its size.

>> >In order to do that i'd need to fill the transaction log quickly,
>> >are there any tricks to perform that (instead of doing 10000 inserts
>> >or updates)?

> >THanks ,
>> >eRwan Pianezza, Galway, Ireland

>> There isn't any way to do that without performing transactions,
>> but you could test your dump threshold on a modestly sized DB
>> and log, like a 20 MB DB with a 5 MB log. At those small sizes,
>> you'll get your threshold results pretty quickly - in a few
>> minutes. More so than trying it on a VLDB.

>Just be careful about using a smaller database than what you're trying to
>test.  We've had problems when the database is filling up quickly that
>the last chance threshold is crossed before the dump completes

Good point. I use "sleep" directives in shell scripts or similar variants in code like
this to keep that from happening.

Bob McIlree



Wed, 26 Apr 2000 03:00:00 GMT
 
 [ 10 post ] 

 Relevant Pages 

1. Transaction Logs & Transaction Management

2. Transaction Log Dumps and Cross Database Transactions

3. Transaction based replication and dumping of transaction logs

4. transaction replication with large transaction log files

5. transaction log filling up and transaction replication

6. ESQL/C transaction logging - outside a transaction?

7. Transaction log grows w/o transactions

8. SQL 7 transaction log logging directly to tape

9. Non-Logged operations causing transaction log restore to fail

10. Recapturing disk space from the transaction log after truncating the log space

11. Log shipping: Auto restore transaction log?

12. Transaction Log Backups Are Slower When The First Portion Of The Log Sequence Number Increments


 
Powered by phpBB® Forum Software