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

Posted on 2006-03-21
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..??

Question by:NewDBA_80203
    LVL 24

    Expert Comment

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

    Author Comment

    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
    LVL 24

    Accepted Solution

    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.

    Expert Comment

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

    Expert Comment

    by: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.

    Expert Comment

    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.

    LVL 24

    Expert Comment

    by: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!

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    If you're not part of the solution, you're part of the problem.   Tips on how to secure IoT devices, even the dumbest ones, so they can't be used as part of a DDoS botnet.  Use PRTG Network Monitor as one of the building blocks, to detect unusual…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now