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

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!)

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

Joe Woodhouse

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

fjvb_2000

Do you really need logging your temporary tables' activity? Remember they are temporary data that probably can be rebuilt easily.

Joe Woodhouse

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.

fjvb_2000

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 Woodhouse

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!