We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


Tempdb issues on Sybase  1176 /P/SWR 9518 ESD on NT - Help please !

NewDBA_80203 asked
Medium Priority
Last Modified: 2008-01-09
Dear Experts,  appreciate if you can give me few inputs,  we have a Sybase server on Windows NT,  with a 15GB database and 600 MB tempdb, we are also running SQL Remote on it, the front end application create lots of temp.tables,  we are having issues with tempdb log/data segemnet getting filled up affecting users, also we get error 216  State:3 (unable to drop temp.tables) sometimes..  

Sybase recomended to use Trace Flag 5303, when I activated the flag,  am unable to truncate the logsegment on tempdb, currently there no active trace flags,  any clue about this kind of behaviour..??

Watch Question

Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012

Firstly I have to say that 600Mb of tempdb for 15Gb of data seems extremely small! I'm not at all surprised that you fill tempdb log and data. The only surprise is that sometimes you don't!

There's little point fooling around with traceflags until your tempdb is a more reasonable size.

It's also usually helpful to separate tempdb data and log just as you would for any regular user database.

Restarting ASE will clear things up. Strongly suggest you return to traceon(5303) if and only if a larger tempdb (try 2Gb) with data & log separated doesn't fix your problem.

(I'm surprised Sybase didn't say the same thing!)


Hello Joe..  sorry  for wrong info, the temp DB is 800 MB  and database is 8 GB
And I tried put trace Flag 5303, but had to remove it since we are unable to truncate log on tempdb.

tempdb                        802.0 MB sa                            2 Mar 23,
006   select into/bulkcopy/pllsort, trunc log on chkpt, ddl in tran, abort tran
on log full

1 row affected)
device_fragments               size          usage                free kbytes
------------------------------ ------------- -------------------- -----------
master                         2.0 MB        data only                   1104
tempdb1                        200.0 MB      data and log              204720
tempdb2                        200.0 MB      data and log              204800
tempdb3                        200.0 MB      data and log              204768
tempdb4                        200.0 MB      data and log              204800

1> sp_helpdb
2> go
 name                     db_size       owner                    dbid

 ------------------------ ------------- ------------------------ ------
------ -----------------------------------------------------------------
 db                          8000.0 MB sa                            5
 dbs                        2000.0 MB  sa                            6
 master                          5.0 MB sa                            1
 model                           2.0 MB sa                            3
 sybsystemprocs            60.0 MB sa                            4
 tempdb                      802.0 MB sa                            2

Space available in the log segment has fallen critically low in database 'tempdb
'.  All future modifications to this database will be aborted until the log is s
uccessfully dumped and space becomes available.
Msg 1105, Level 17, State 4:
Line 7:
Can't allocate space for object 'syslogs' in database 'tempdb' because 'logsegme
nt' segment is full/has no free extents. If you ran out of space in syslogs, dum
p the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to incr
ease size of the segment.

2> dump tran tempdb with no_log   (unable to truncate with trace flag on)
3> go

1> sp_helpdb tempdb
2> go
 name                     db_size       owner                    dbid   created

 ------------------------ ------------- ------------------------ ------ -------
------ ------------------------------------------------------------------------
 tempdb                        802.0 MB sa                            2 Mar 23,
2006   select into/bulkcopy/pllsort, trunc log on chkpt, ddl in tran, abort tra
 on log full

(1 row affected)
 device_fragments               size          usage                free kbytes
 ------------------------------ ------------- -------------------- -----------
 master                              2.0 MB        data only                   1104
 tempdb1                        200.0 MB      data and log                 896
 tempdb2                        200.0 MB      data and log                5088
 tempdb3                        200.0 MB      data and log                5200
 tempdb4                        200.0 MB      data and log                8848
(return status = 0)

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

2.  Is there any script/storedproc available, which I can integrate with sp_tresholdaction procedure, so that I can catch the spid's with high cpu and physical_io which resulted in filling up the logsegment,

Or is there any better way to monitor the process which is filling up the tempdb and reflect it on errorlog..?
Any suggestions ?

Thanks & Regards
Principal Consultant
Most Valuable Expert 2012
I notice you have "ddl in tran" enabled in tempdb. This isn't always a good idea, depending on your transactions, as it can end up with locks held on tempdb system tables for quite a while. But that probably isn't your problem here, it seems you genuinely are just plain running out of space.

With data and log mixed like that, it's hard to tell what the issue really was. Yes, the error message complained about running out of log space, but that just means it was a log write that was the one that failed. It might have been because tempdb was 99% full of data already, say.

Even 800Mb out of 8Gb seems small to me. It's rare these days I'd build a production system with less than 1Gb of tempdb (unless the production databases were only 500Mb!). I still think your problem here is (1) data & log are mixed in tempdb, and (2) tempdb is too small.

If you have the disk space, I'd suggest you rebuild tempdb as 1Gb of data and 1Gb of log.

Sybase doesn't ship any code as a sample threshold procedure, but I should point out that until you have data & log separated any threshold monitoring will be distorted... In my scenario above, where some process A has filled your mixed tempdb 99% full of data, and process B needs to write a measly 100 log records, process B will be the one that "fills the log" and most monitoring would flag it - but "A" was the problem.

I don't have any code handy, but in principle you want to lookup master..syslogshold and master..sysprocesses based on dbid = @dbid (mandatory parameter in any threshold action procedure). The Sybase manuals cover this well. Sorry I don't have details.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Do you really need logging your temporary tables' activity? Remember they are temporary data that probably can be rebuilt easily.
Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012

We should add for completeness that there is no way to disable logging in tempdb (or indeed, in any other database); we're stuck with it, and must manage it.

However there is a good argument that we probably don't need explicit transactions for temp tables.
If I am not mistaken, select into is a nonlogged operation and is also able of creating temporary tables. Maybe this is a way of creating the temporary tables without logging problems.

Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012

Nitpick - select into is *minimally* logged (logs page allocations only, not the row inserts to those pages). There are no completely unlogged writes in Sybase.

Definitely select into is the preferred option for many reasons!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.