tempdb full

Hi

Some query fills my tempdb (system segment), probably a huge 'order by' 
clause. I'm using 12.0 , so I can't use the 'resource governor' available 
in 12.5.

This blocks my whole ASE !!! I can't even do sp_who 

How can I find out WHO is filling tempdb and subsequently killing the 
offending process? I'm thinking of a threshold procedure on the system 
segment of tempdb, unlike the logsegment, there's no system table to query 
from for the 'system segment'. (for the log-segment, there's the 
syslogshold table)

Is there any way to check who's filling tempdb ??

Ton
0
TonF
10/29/2002 2:04:25 PM
sybase.ase.administration 7058 articles. 2 followers. Follow

8 Replies
1377 Views

Similar Articles

[PageSpeed] 17

<TonF> wrote in message
news:96FD3CF738ED7484004D4F1985256C61.004D4F2C85256C61@webforums...
> Hi
>
> Some query fills my tempdb (system segment), probably a huge 'order by'
> clause. I'm using 12.0 , so I can't use the 'resource governor' available
> in 12.5.
>
> This blocks my whole ASE !!! I can't even do sp_who
>
> How can I find out WHO is filling tempdb and subsequently killing the
> offending process? I'm thinking of a threshold procedure on the system
> segment of tempdb, unlike the logsegment, there's no system table to
query
> from for the 'system segment'. (for the log-segment, there's the
> syslogshold table)
>
> Is there any way to check who's filling tempdb ??
>
> Ton

Just run "select lct_admin("abort", 0, 2)" -- this will abort the
transactions causing the log suspend condition.

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0

Author of "The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/qr

mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------


0
Rob
10/29/2002 2:45:54 PM
TonF wrote...
> Hi
> 
> Some query fills my tempdb (system segment), probably a huge 'order by' 
> clause. I'm using 12.0 , so I can't use the 'resource governor' available 
> in 12.5.
> 
> This blocks my whole ASE !!! I can't even do sp_who 
> 
> How can I find out WHO is filling tempdb and subsequently killing the 
> offending process? I'm thinking of a threshold procedure on the system 
> segment of tempdb, unlike the logsegment, there's no system table to query 
> from for the 'system segment'. (for the log-segment, there's the 
> syslogshold table)
> 
> Is there any way to check who's filling tempdb ??

Just because you can't run sp_who doesn't mean you can't run other queries.  Do a SELECT * 
FROM sysprocesses without any order by.
-- 
Jim Egan [TeamSybase]

0
Jim
10/29/2002 4:17:52 PM
"Jim Egan" <dontspam.dbaguru@eganomics.com> wrote in message
news:MPG.182862987e0a6d4d98bff3@forums.sybase.com...
> TonF wrote...
> > Hi
> >
> > Some query fills my tempdb (system segment), probably a huge 'order by'
> > clause. I'm using 12.0 , so I can't use the 'resource governor'
available
> > in 12.5.
> >
> > This blocks my whole ASE !!! I can't even do sp_who
> >
> > How can I find out WHO is filling tempdb and subsequently killing the
> > offending process? I'm thinking of a threshold procedure on the system
> > segment of tempdb, unlike the logsegment, there's no system table to
query
> > from for the 'system segment'. (for the log-segment, there's the
> > syslogshold table)
> >
> > Is there any way to check who's filling tempdb ??
>
> Just because you can't run sp_who doesn't mean you can't run other
queries.  Do a SELECT *
> FROM sysprocesses without any order by.
> --
> Jim Egan [TeamSybase]

That won't work: when tempdb is full, you cannot select from the 'fake'
tables in the master DB.

Rob V.


0
Rob
10/29/2002 4:23:51 PM
rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY wrote...
> That won't work: when tempdb is full, you cannot select from the 'fake'
> tables in the master DB.

Oh yeah.  I'm thinking of full user db logs not tempdb.
-- 
Jim Egan [TeamSybase]

0
Jim
10/30/2002 4:02:19 AM
I've tried it. That doesn't work either in that case ... 

Unfortunately,  there's basically nothing I can do.
0
TonF
10/30/2002 3:51:16 PM
Thanks, Rob

But from the manual pages of lct_admin, I understand that this only works 
for the LOGsegment. (quote: lct_admin, a system function, manages the log 
segment's last-chance threshold.)

My problem is not the log segment, but the system segment of tempdb. Once 
this is filled, the ASE basically locks up.


Ton
0
TonF
10/30/2002 4:07:40 PM
I also got the same error of system segment of tempdb getting filledup for 
the first time yesterday.  We are using ASE 11.9.2.4.  This error was 
triggered by a process which runs daily. It gave me the same 1105 msg when 
reran the process,  but went fine when i added 500 MB of space to tempdb 
for data.  And also recycled the server.

But would like to know what makes the system segment filledup normally???


-Subbu
0
Subbu
10/30/2002 4:26:51 PM
<TonF> wrote in message
news:8739A854C0B61B77005897E185256C62.005858EB85256C61@webforums...
> Thanks, Rob
>
> But from the manual pages of lct_admin, I understand that this only works
> for the LOGsegment. (quote: lct_admin, a system function, manages the log
> segment's last-chance threshold.)
>
> My problem is not the log segment, but the system segment of tempdb. Once
> this is filled, the ASE basically locks up.
>
>
> Ton

In pre-12.5, there's no way to figure out who uses space for worktables
(for 'order by' etc) in tempdb.
I take it from your reply that you have separated the tempdb log from the
default/system segments ?

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0

Author of "The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/qr

mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

0
Rob
10/30/2002 5:05:21 PM
Reply:

Similar Artilces:

Replication ASA-ASE stops because ASE is full
I have a replication between a consolidated DB on ASE and 4 remote DB on ASA. During a replication, ssremote was unable to apply messages from the remotes because the segments of the consolidated DB where full. The message of ssremote was : E. 03/31 06:00:06. bourget: Error 1105: Can't allocate space for object 'depot' in database 'stp_qual' because 'default' segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase size of the segment. I have increas...

full tempdb
Does anybody know how to log in to ASE if temdb is full I heard there is a undocummented dataserver command who attaches to the shared memory and so you can log in. Many thanks for any help kai On 9 Oct 2003 05:33:03 -0700, in sybase.public.ase.administration <Kai> wrote: >Does anybody know how to log in to ASE if temdb is full >I heard there is a undocummented dataserver command who >attaches to the shared memory and so you can log in. >Many thanks for any help >kai Hi Kai, the undocumented tool (SybMon) won't allow you to log into the server. I...

ASE to ASE
Hi, Is anyone out there doing ASE to ASE replication using SQL remote? If you are have created your own version of the SSEXTRACT utility to set up a replicant database in ASE rather than ASA? Or does anyone know if SYBASE has created a version SSEXTRACT for use with ASE to ASE replication? Thanks in advance Doug Trainer Hi Douglas, I thought the SQL Remote Replication support in ASE11.5 was meant for a consolidate-database only (by design)! At least that was what Sybase said when they announced support for SQL Remote technology support in ASE11.5. I will be happy to...

tempdb is full
Hi, I need to know how to capture the query that full up the tempdb database when is at 80%. Thanks Willy Hi, There are a few alternatives oyu can use for that purpose: 1-) add a threhold (calculate how many pages remains when the segment in tempdb is 80% per. full) for the segment you are interested in and insert data related to spid filling up the tempdb into a table located in a user database you got. 2-)(from master..syslogshold where dbid=2) when you find out the spid of the process filling up tempdb, use the following commands to retrieve sql text: dbcc traceon(3604) ...

ASE and ASE
Could it be possible to take scripts from ASA and load them in an ASE Any information is welcome jean-fran�ois ASA supports a fairly large sub-set of Transact-SQL, so if you write your stored procedures and triggers in ASA using T-SQL, you should be able to create scripts that will run against both ASE and ASA. If you're planning to do this though, I would suggest developing your database schema against ASA, since everything you write in T-SQL in ASA will be supported on ASE, but the reverse is not true. Check out the section in the ASA documentation entitled "Tra...

ASA To ASE - VS
What are the differents in terms of configuring SQL Remote Between ASA to ASE and ASE To ASE ? You should start with the Help file and then ask specific questions: Data Replication with SQL Remote PART 5. Appendix APPENDIX A. Enterprise and Anywhere: Differences There are many differences, so begin there. -- David Fishburn Sybase Please only post to the newsgroup BH Ong <bhong@tm.net.my> wrote in message news:01bf4b98$33183580$7ccdc8c8@virtual-branch... > What are the differents in terms of configuring SQL Remote Between ASA to > ASE and ASE To ASE ...

Tempdb Full #5
Could anyone tell about the procedure , if the tempdb get full in production system? ( Apart from bouncing the server ) Bal, Kumar wrote: > Could anyone tell about the procedure , if the tempdb get > full in production system? ( Apart from bouncing the server > ) You can use ALTER DATABASE to increase the size of tempdb. Long term, if it is getting full because of regular objects getting created and then abandoned by code, you will need to figure out what code is creating those objects and whap the developers on the head, make them use #temp tables. What ...

tempdb full #4
How to know what spid was responsible of filling the tempdb?? the error logs show nothing relevant about that and when tempdb is full, sybase hangs when I run all kinds of stored procedures. thx below information might help you tempdb - server-wide temporary database ASE 12.5.0.1 1. Is a temporary database and rebuilt from model template 2. By default tempdb resides on master device, extend on separate device 3. Heavy contention in tempdb due to server-wide temporary objects 4. Bind tempdb to named cache in case of heavy usage/contention 5. When tempdb is full and no spa...

Tempdb Full #2
The tempdb on my server is fuill ? What are the options which I have now ? Whether I should just reboot the server ? Whether I can somehow create space in tempdb even if it is full now Thanks Amit You'll have to reboot! Try turning on "abort transaction on full log" on the tempdb database - this will at least allow your server to continue running You can alter tempdb, making it larger, even if it is currently full - you can do that with any database except master. -bret Amit wrote: > The tempdb on my server is fuill ? What are the options which I ha...

tempdb runs full
Does anybody know how i can avoid that tempdb runs full during several heavy SQL statements? I guess i have to increase the size of it, but i'm not sure how i do that the right way. Any suggestions? ASE: 11.0.3 OS: Linux Redhat 6.1 -- Regards Oystein Selbekk any questions: 1. What is the size of your tempdb. 2. What is the size of the tables and result sets of your SQL-statements? If you use 'order by - having' or 'sort by', than the result is buffered in the tempdb Torsten Zielinski "�ystein Selbekk" <oystein@reiselivdata.st.no&...

tempdb log full
We have an application that is using a lot of temp tables. The database structure is as follows: 6 GB data 1 GB log 1 GB for tempdb (data and log) A couple of times the tran log on tempdb has got full and to truncate it we have had to recycle the server. We were not able to check what transaction was filling up tempdb tran log because we were not able to execute any query on sysprocesses, syslocks or sysholdlocks. We are usign ASE 11.9.2 for Unix. Does anyone know if there is a bug on ASE? Thanks Sergio wrote: > > We have an application that is using a lot of...

tempdb log full.
Our system is ASE 11.5.1 and SWR8155 and it is running on HP-UX 11. When the logsegment of the tempdb is full, I cannot kill the process which fills the log. And I have to shutdown the SQL server with nowait option. Does anybody help me about why I cannot kill the process which fills up the tempdb log? And is there a way clearing the tempdb logs without shutting down the server (And also dump tran with nolog does not work in this situation) Thanks for your help, Deniz Ata wrote: > > Our system is ASE 11.5.1 and SWR8155 and it is running on HP-UX 11. > When the lo...

Web resources about - tempdb full - sybase.ase.administration

Microsoft SQL Server - Wikipedia, the free encyclopedia
Microsoft SQL Server is a relational database management system developed by Microsoft . As a database, it is a software product whose primary ...

SQL中的图树层次结构_开发/数据库_IT专家网 开发/数据库
数据库子站,提供数据库管理软件优质技术信息的领先在线资源,是数据库管理员、开发人员和负责决策制定的 IT 经理人至关重要的社区。我们提供具有特色的、全面的内容,包括专家答疑、讨论论坛、白皮书、原创的行业新闻、评论分析以及技术技巧等。还有众多国内外数据库专家定期为我们的网友在线答疑,让我们的沟通学习更为方便。 ...


An Interview With Ken Henderson About The Forthcoming Book SQL Server 2005 Practical Troubleshooting ...
I am a big fan of Ken Henderson’s books, I believe that every SQL Server developer should have a copy of his books. When I noticed on Amazon.com ...

Frequently Asked Questions About TempDB
The questions came fast and furious in one of my recent TempDB webcasts, so here’s the ones I wasn’t able to answer during the live session: ...

Controlling Transactions and Locks Part 4 : SQL 2005 Snapshot
The first three installments of this series applied to both SQL 2000 and SQL 2005. Part 4 introduces Snapshots, new in MSSQL 2005.

How to move databases between computers that are running SQL Server
Describes how to move databases between computers that are running SQL Server. Provides step-by-step information about the method.

In-memory OLTP Engine (Hekaton) in SQL Server 2014 CTP2
Today I am going to discuss the new SQL Server 2014 feature In-memory OLTP a.k.a. Hekaton a Greek word which means hundred or hundredfold. In-memory ...

Texas Memory Systems: Applications
SQL Server Databases Accelerate your transaction logs, temporary database (tempdb), indexes, and frequently accessed tables with a RamSan SSD ...

Email to Author
Please use this form to email the author of this article.

Resources last updated: 12/21/2015 4:09:45 PM