Showing results for 
Search instead for 
Did you mean: 

RDDB for tempdb database (15.7) ?

0 Kudos

Hi All,

I read within IMDB docs that temporary databases will highly benefit of this feature.

I tested this with user temporary db and it worked with substantial improvements.

But I could not manage to put dml_logging minimal on standard tempdb database , with or without 'dbo use only' , with another sarole bound to another tempdb . Everytime I get 'database in use' and I cannot apply this option. I tried with putting model db with this option and restart , without success.

Is anyone who tried this and succeeded ?

I think I will ask support.



Accepted Solutions (0)

Answers (3)

Answers (3)

Active Contributor
0 Kudos

There is a documentation error, CR 762224.  This dml_logging option cannot be set on the system tempdb.


0 Kudos

Hi Bret,

thanks for your answer.

I am aware of that, this KBA was created following my questions and an error in documentation.

But here, I have put dml_logging on user created tempdbs, not system tempdb. It has worked, I have sysmon reports with ML discards , but now, perhaps due to some config parameter , I can not get discards again, and when enabling diganostics , I get this message.

This msg is about tempdb , but tempdb is not not with option dml_logging minimal...

That's why I ask someone for an idea about this strange behaviour.


0 Kudos

Hi All,

I have some news after testing :

-- with same config an temporary imdb is OK, i got discards

  Minimally Logged DMLs

    by ULC Discards                 230.4          87.8        1843      99.6 %

-- a new db , not temporary, not imdb , created with durability = no_recovery , dml_logging = minimal

I got discards, too

-- a NEW temporary, not imdb, AND created with durability = no_recovery , dml_logging = minimal

I got discards , too

-- Attempting to apply dml_logging option to an existing tempdb (with dbo use only as explained in doc) -> I never got discards

And , when executing sp_helpdb , you can notice something new for me , look at tempdbtest :

tempdb_imdb   user created temp db, minimal dml logging, in-memory, allow wide dol rows
tempdb_user   user created temp db, minimal dml logging, allow wide dol rows
tempdbtest    user-created enhanced performance temp db, minimal dml logging, allow wide dol rows

In 15.5 , I succeeded to get discards; without need of recreating from scratch my temporary db, and I don't rember this "enhanced performance tempdb".




Active Contributor
0 Kudos

sp_helpdb was enhanced in 15.7 SP50 and SP100 to output this "user-created enhanced performance  temp db" status.  Prior to this change the durability setting still caused a difference in the sysdatabases.status4 field, sp_helpdb just didn't reveal the difference.

Select name from sysdatabases where status4 & 8192 = 8192  will show the databases where the durability level of "no_recovery" was explicitly set when the database was created.

0 Kudos

Yes, I have seen that in sp_helpdb code.

Just to mention we have "dml_logging minimal" and "no_recovery" displayed  , but only tempdbs created with no_recovery -- enhanced performance -- , will really use minimally logging, for others it is silently ignored.

Why accept "alter database set dml_logging = minimal" in this case ?  Better to clarify this db must have been created with explicit no-recovery. It would have been less confusing.



0 Kudos

Hello All,

I eventually put dml_logging on some user temporary created database , but I could not notice discards in sysmon output.

After some searches within documentation, I managed to activate diagnostics about why not using minimally dml and here is what I got :

"Database 'tempdb' is not eligible for IMDB performance features, possibly due to inactive license. Use FULL logging"

1) my licence seems OK , no msg from sysam in errorlog and , sp_ehlpdb shows dm_logging minimal

2) It's not tempdb which has dml_logging but some others databases whose name begins with tempdb...

Has someone an idea ?

I succeeded to get discards but now I don't know why I no longer get them.



Former Member
0 Kudos

Hi Marc,

The documented say that you need to have dbo-use only for temporary databases and single user mode.

The message say some one is use this temporaty database.

You can alter the default logging mode for a database only from the master database. In addition, the database you are altering must:

  • Be in single-user mode for user databases
  • Have the dbo-use only set to true (on) for temporary databases, so that only the database owner can use the databases

If the database is not already in the required mode, the server tries to put the database in that mode. If the attempt is unsuccessful, the server raises an error, prompting the user to explicitly put the database in its correct mode.

see  Database-level logging

Could you provide the ASE version  and the output from sp_tempdb.


0 Kudos

HI Niclas,

Thanks for your interest and reply .

Here is some requested information.

I am alone in dataserver .

4> select @@version

5> go



Adaptive Server Enterprise/15.7/EBF 21707 SMP SP110 /P/ia64/HP-UX B.11.31/ase157sp11x/3546/64-bit/FBO/Fri Nov  8 08:40:39 2013                                                                     

(1 row affected)

1> sp_tempdb 'show'

2> go

Temporary Database Groups



(1 row affected)

Database     GroupName

------------ -----------

tempdb       default


(2 rows affected)

Login   Application   Group            Database     Hardness

------- ------------- ---------------- ------------ -------------------

sa      NULL          NULL             tempdbsarole SOFT

sarole  NULL          NULL             tempdbsarole SOFT

(2 rows affected)

(return status = 0)

1> sp_helpdb

2> go

name           db_size       owner dbid  created      durability  lobcomplvl inrowlen status                                                                                                       

-------------- ------------- ----- ----- ------------ ----------- ---------- -------- ---------------------------------------------------------------------------------------------------------------

master              250.0 MB sa        1 Mar 17, 2014 full                 0     NULL mixed log and data                                                                                           

model                12.0 MB sa        3 Mar 17, 2014 full                 0     NULL mixed log and data                                                                                           

sybsystemdb          92.0 MB sa    31513 Mar 17, 2014 full                 0     NULL mixed log and data                                                                                           

sybsystemprocs      250.0 MB sa    31514 Mar 17, 2014 full                 0     NULL trunc log on chkpt, mixed log and data                                                                       

tempdb             8012.0 MB sa        2 Apr 01, 2014 no_recovery          0     NULL select into/bulkcopy/pllsort, trunc log on chkpt, dbo use only, mixed log and data, allow wide dol rows      

tempdbsarole        200.0 MB sa        6 Apr 02, 2014 no_recovery          0     NULL select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data, user created temp db, allow wide dol rows

(1 row affected)

(return status = 0)

1> alter database tempdb set dml_logging = minimal

2> go

Msg 16052, Level 16, State 2:

Server 'AMD2_PAR_IST_SQL', Line 1:

Cannot change the DML_LOGGING option of the database 'tempdb' because the database is in use. Retry the command when there are no users in the database, or use sp_dboption 'tempdb', 'dbo use only', 'true' to put the database in 'dbo use only' mode.



0 Kudos

HI All ,

I asked support and they cannot manage to apply what is written in documentation.

So , we have to cope with the fact it's currently not possible to get minimal dml_logging for system tempdb , and then we have to manage user tempdbs and groups and binds to take advantage of this feature.

If someone has other news, welcome !!



0 Kudos

FWIW, there is an existing feature request open with SAP under CR 727562 to move the system tempdb to be IMDB by default (or at least have the option),. You should request to be linked to that CR. As far as I know there are no plans to implement it currently.

Former Member
0 Kudos

Hi Marc,

Follow up to what Simon said about CR 727562. "moving the system tempdb
to be IMDB by default",

I not sure the dml_logging as an option for a tempdb will give you a big performance
improvement because already in version 15.0.2 we reduced logging for tempdb.

Server Enterprise 15.7 SP100
> New Features Summary Adaptive Server Enterprise 15.7 SP100 > Version 15.0.2 > Improved Performance for Data Insertion

Improving Throughput of tempdbTransactions

Earlier versions of Adaptive Server flushed the data
pages and single log records (SLRs) because crash recovery was not supported
for tempdb or any databases not
requiring recovery.

SLRs are log records that force a flush of the user log
cache (ULC) to syslogs
immediately after the record is logged. SLRs are created for OAM modifications,
and Adaptive Server creates log records affecting allocation pages in a mixed
log and data database as SLRs.

  • For regular databases, a
    ULC containing SLRs is flushed immediately to avoid any undetected deadlocks
    caused during buffer “pinning”. Avoiding a ULC flush for SLRs reduces log
    semaphore contention, improving the performance.

A ULC flush avoids the
deadlock caused by buffer pinning. Because Adaptive Server does not pin the
buffers for databases that do not need recovery, it avoids this deadlock and
does not have to flush the ULC for SLRs.

  • For databases that
    require recovery, Adaptive Server flushes dirty pages to disk during the
    checkpoint. This ensures that, if Adaptive Server crashes, all the committed
    data is saved to disk. However, for databases which do not require recovery,
    Adaptive Server supports a runtime rollback, but not a crash recovery. This
    allows it to avoid flushing dirty data pages at a checkpoint and improves
  • Adaptive Server does not
    support write ahead logging on databases that do not require recovery.
    Write-ahead logging guarantees that data for committed transactions can be
    recovered by “redoing” the log (reperforming the transactions listed in the
    log), and “undoing” the changes done by aborted or rolled back transactions to
    maintain database consistency. Write-ahead logging is implemented by the
    “buffer pinning” mechanism. Since Adaptive Server does not ensure write-ahead
    logging on databases not needing recovery, it does not pin buffers for these
    databases, so it can skip flushing the log when it commits a transaction.

We also have added the session tempdb
log cache size in ASE 15.7.

What I mean with this is that tempdb as IMDB will give you performance improvement
Simon maybe can give more number about that !!  but I do not think that dml_logging is the
major factor for this improvement.


0 Kudos

First - one needs to remember that there have been a lot of tempdb optimizations that have already been applied - so.....many of the RDDB options may not be all that applicable....

For example, you need to remember, that tempdb already has a ton of stuff to optimize it....going back to the 12.5's, if you did a select * into #temp and the select could use parallel worker threads, ASE would partition the #temp under the covers and do the bulk inserts in parallel and then unpartition the table (there was a trace flag that a lot of sites enable to prevent this unpartitioning due to perception of system table contention at the time...while it did add to it, the partitioning so rarely happened it wasn't the main cause).   Then in 15.0.x, ASE added lazy IO's......and then delayed commit was essentially enabled by default for tempdb.   Then 'session tempdb log cache' was added - which avoids logging altogether if the txn log records fit within the session tempdb ULC size - something most DBA's forget to enable.  I don't understand is kinda like throwing away free money.

The problem with RDDB and minimal logging is that it makes it IMPOSSIBLE to rollback transactions correctly.    There are times when this is necessary.....remember, tempdb is used for a lot of system stuff other than default for #temps.   For example, fully prepared statements are in tempdb - as are work tables/store indexes, etc.    Also, you need to be really careful - think of what would happen if tempdb was only an IMDB and it filled would you fix it???   Remember, if tempdb fills, even common procs such as sp_who stop working - so you couldn't even get a list of spids to kill hoping to clean up #temp tables.

However, of course, you *can* create your own user defined tempdb that is both IMDB and minimal_logging if you wish.   Creating your own tempdb really isn't that hard to do.  SAP does this by default - they create a saptempdb for all users and leave tempdb just for the system stuff and LWP's that are created from all the prepared statements.    It really is much much safer that way...and something I encourage.   Leave tempdb for system use and sa_role stuff when critical recovery is necessary - create your own tempdb (or more than one) and bind users to it.    It really isn't all that difficult.

0 Kudos

I think the reasoning behind the request to allow tempdb to be in-memory is two fold.

The process of application login creation for a dataserver is already fairly arduous with a ton of audit requirements and sign-off before approval is granted. Amending this process to also include step to configure & bind to specific temporary databases is technically straightforward, but a bureaucratic nightmare.

If there was an option to NOT bind certain logins/applicationsthat would make maintenance much more straightforward (and may even negate the need for the imdb tempdb?) I.e, bind everything apart from sa_role logins etc.

On a given dataserver there may be 10000 logins and a thousand applications. Many legacy or tactical apps are difficult to track down to particular groups and establishing clear ownership and responsibility is not always straightforward. Whilst I was working on your side, I too, always used to say, it isn't difficult 🙂 It's the rigmarole around it, particularly when there are ever-tightening controls that make things difficult.

As you say, there is still always going to be activity on the system tempdb, in which case how should it be sized assuming you did move everything you could across to imdbs? As for lwps, they are assigned a database id of 2, but are they really 'in' tempdb, in as much as taking space in the system segment? The plan will be in the proc cache and there will be an object structure but I don't think it'son-disk. (?). I guess if tempdb were imdb, then as you say there would have to be a mechanism to be able to extend it.

0 Kudos

Hello Everybody,

Thanks for your interest for this subject. Finally , I decided for user temporary dbs, either RDDB or IMDB depending of RAM amount I can afford, as I really saw with sysmon a big number of log access avoided and ulc flushes skipped. I fortunately found two appnames which will allow me to avoid managing several hundreds of logins.

I can understand risks by putting system tempdb as imdb , perhaps the ability to bind a group (here users group) , added to login and application name, to user tempdb (or tempdbs group) will make easier unsing this feature.