Hi all

If this is impossible, I will be gobsmacked.

Can someone *please* tell me how to disable transaction logging in
tempdb? We have absolutely zero interest in point in time recovery on
this DB, and all the transaction log is doing is causing us
performance and space issues.

Please? Anybody?

On Apr 9, 6:18*am, sco...@amnet.net.au wrote:
> Hi all
>
> If this is impossible, I will be gobsmacked.
>
> Can someone *please* tell me how to disable transaction logging in
> tempdb? We have absolutely zero interest in point in time recovery on
> this DB, and all the transaction log is doing is causing us
> performance and space issues.
>
completely disabling transaction logging is not possible in ASE.
However sizing tempdb appropriately reduces the overhead
cosiderably.You also have option to create "user tempdb" to
distribute tempdb load across databases.If you dont care about point
in time recovery, to reduce tempdb log size you can execute dump
transaction process with no_log in loop which will truncate log as
often as *possible* .

-HTH
Manish Negandhi
[TeamSybase Intern]

On Apr 9, 10:41 am, Manish Negandhi <negandhi.man...@gmail.com> wrote:

> in time recovery, to reduce tempdb log size you can execute dump
> transaction process with no_log in loop which will truncate log as
> often as *possible* .

Thanks for the response!

Will no_log dump the transaction log of current sessions?

The main issue we have is we have several processes that do an
extremely large amount of work in tempdb, but we do not wish to retain
the ability to rollback the main database (not tempdb). So our
transaction log issues are from single processes......

We could increase the size of the log again, but it all seems so
fruitless when you care zero about the log.

On Apr 8, 8:54 pm, sco...@amnet.net.au wrote:
> On Apr 9, 10:41 am, Manish Negandhi <negandhi.man...@gmail.com> wrote:
>
> > in time recovery, to reduce tempdb log size you can execute dump
> > transaction process with no_log in loop which will truncate log as
> > often as *possible* .
>
> Thanks for the response!
>
> Will no_log dump the transaction log of current sessions?
>
> The main issue we have is we have several processes that do an
> extremely large amount of work in tempdb, but we do not wish to retain
> the ability to rollback the main database (not tempdb). So our
> transaction log issues are from single processes......
>
> We could increase the size of the log again, but it all seems so
> fruitless when you care zero about the log.

dump tran can only truncate the log up to the beginning of the oldest
open transaction.

You really cannot turn off logging in tempdb. really.

15.0.2 has a new feature that reduces the amount of logging i/o that
has to be done in tempdb, which will help with the performance side of
things. I believe we are also looking at ways to make tempdb(s)
purely memory-resident with no need for disks at all - but the feature
isn't here yet.

-bret

On Apr 9, 11:47 am, b...@sybase.com wrote:
> You really cannot turn off logging in tempdb. really.
>

Thanks for that. Ill add it to my document listing the reasons why we
should switch to Oracle

Seriously, much obliged.

Hello, Bret --

On Apr 8, 8:47 pm, b...@sybase.com wrote:

> I believe we are also looking at ways to make tempdb(s)
> purely memory-resident with no need for disks at all - but the feature
> isn't here yet.
>
> -bret

Somebody told me you could set up a ram-disk, and mount the tempdb on
it.

I tried it without much success. But then, they never told me how it
was supposed to be accomplished.

Someone else told me you could simply install a lot of memory, then
make a very large cache, then assign said large cache to the tempdb.
It's not really a "ramdisk", but Sybase should keep things in the
cache until it's necessary to save it to disk. If the cache is as
large as tempdb, that need will be very rare. This is supposed to
have similar performance to a ramdisk.

I've tried this, and it appears to work, although I haven't
benchmarked it.

Comments?

<scotty@amnet.net.au> wrote in message
news:ff2ce15a-acd1-4fe4-a77a-ad44e106411e@q1g2000prf.googlegroups.com...
> Hi all
>
> If this is impossible, I will be gobsmacked.
>
> Can someone *please* tell me how to disable transaction logging in
> tempdb? We have absolutely zero interest in point in time recovery on
> this DB, and all the transaction log is doing is causing us
> performance and space issues.
>
> Please? Anybody?
>

You also has a followup reply with:

"The main issue we have is we have several processes that do an
extremely large amount of work in tempdb, but we do not wish to retain
the ability to rollback the main database (not tempdb). So our
transaction log issues are from single processes......"

I'm confused as to whether your issue is with logging in tempdb or your main
database or both. Apparently it is with both?

Your ASE version might be helpful to readers of this forum. In general, the
newer the version the more likely that a new feature may be helpful. Or
that we notice issues with the release that you are using (i.e., recommend a
downgrade or upgrade).

Does the application use "create #table ... and insert into #table ..."? Or
"select ... into #table"? I'm a little bit rusty with the various ASE
releases but the latter should be minimally logged whereas the former is
logged. It might be that a bunch of consecutive "select into"s and "drop
previous #table" may help although the code could become somewhat
convoluted. (Instead of "update #table1" do "select into #table2 ...
including computations".)

There have been various threads at ISUG about the benefits of seperating the
system/default/log segments for tempdb as well as having seperate caches for
system/default and log segements of tempdb. Although you may not care about
recoverability of your main database not seperating the log onto its own
devices can impede performance.

On Apr 8, 11:53 pm, ThanksButNo <no.no.tha...@gmail.com> wrote:
> Hello, Bret --
>
> On Apr 8, 8:47 pm, b...@sybase.com wrote:
>
> > I believe we are also looking at ways to make tempdb(s)
> > purely memory-resident with no need for disks at all - but the feature
> > isn't here yet.
>
> > -bret
>
> Somebody told me you could set up a ram-disk, and mount the tempdb on
> it.
>
> I tried it without much success. But then, they never told me how it
> was supposed to be accomplished.
>


There is a technote on it at http://www.sybase.com/detail?id=20448

The quick overview is:
You create a tmpfs file system in memory.
You disk init an ASE device onto it.
You make sure the OS is configured so the tmpfs is created on reboot
You modify the RUN_SERVER file to "touch" the ASE device into
existence before starting ASE.
After that, it is usually smooth sailing.



> Someone else told me you could simply install a lot of memory, then
> make a very large cache, then assign said large cache to the tempdb.
> It's not really a "ramdisk", but Sybase should keep things in the
> cache until it's necessary to save it to disk. If the cache is as
> large as tempdb, that need will be very rare. This is supposed to
> have similar performance to a ramdisk.
>
> I've tried this, and it appears to work, although I haven't
> benchmarked it.
>
> Comments?

This is a good approach, and with the new feature in 15.0.2, the need
to write to disk has been reduced.

In both cases, the key is the "when necessary to save to disk". Even
with a ram disk, writing a page from cache to the ram disk takes some
time - even though it is much faster than writing to a hard drive.
And it is using memory for both the data cache used by tempdb and the
tmpfs, which isn't optimal. And currently, even if everything stayed
in cache and was never flushed to disk, the disks do still have to be
out there to create/alter tempb onto. So it would be ideal if
tempdbs could be created solely in memory and never need a
corresponding "disk" to flush to. I think it is doable, but does
require writing special code for handling tempdb.

<scotty@amnet.net.au> wrote in message
news:d5cde1ff-9cef-43bd-ae76-9e4758dd3544@s33g2000pri.googlegroups.com...
> On Apr 9, 10:41 am, Manish Negandhi <negandhi.man...@gmail.com> wrote:
>
>> in time recovery, to reduce tempdb log size you can execute dump
>> transaction process with no_log in loop which will truncate log as
>> often as *possible* .
>
> Thanks for the response!
>
> Will no_log dump the transaction log of current sessions?
>
> The main issue we have is we have several processes that do an
> extremely large amount of work in tempdb, but we do not wish to retain
> the ability to rollback the main database (not tempdb). So our
> transaction log issues are from single processes......
>
> We could increase the size of the log again, but it all seems so
> fruitless when you care zero about the log.

In addition to my previous response you may want to examine the SQL used in
the database. I all-to-well remember an application that did

update <table>
set colN = [value]
where ...

However it did not include "and colN <> [value]" in the where clause. There
were gazillions of updates of colN from [value] to [value] and, yes, those
logical non-updates are physical updates and are logged.