Link to home
Start Free TrialLog in
Avatar of NewDBA_80203
NewDBA_80203

asked on

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

Dear Experts,  appreciate if you can give me few inputs,  we have a Sybase server 11.9.2.4 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..??

TIA
Kay
Avatar of Joe Woodhouse
Joe Woodhouse

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!)
Avatar of NewDBA_80203

ASKER

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
       status

 ------------------------ ------------- ------------------------ ------
------ -----------------------------------------------------------------
-----------------------------
 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
       status

 ------------------------ ------------- ------------------------ ------ -------
------ ------------------------------------------------------------------------
-----------------------------
 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
Kay.
ASKER CERTIFIED SOLUTION
Avatar of Joe Woodhouse
Joe Woodhouse

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Do you really need logging your temporary tables' activity? Remember they are temporary data that probably can be rebuilt easily.
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.

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!