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

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..??

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joe WoodhousePrincipal ConsultantCommented:
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!)
NewDBA_80203Author Commented:
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
Joe WoodhousePrincipal ConsultantCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Do you really need logging your temporary tables' activity? Remember they are temporary data that probably can be rebuilt easily.
Joe WoodhousePrincipal ConsultantCommented:
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 ConsultantCommented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.