tempdb full

Last week, for one of our production servers stopped responding. On logging 
in , found that tempdb is full and we were forced to shutdown the server.
The error log read as follows:
 
" 02:00000:00259:2001/03/16 14:10:24.63 server  Space available in the log 
segment has fallen critically low in database 'tempdb'.  All future 
modifications to this database will be suspended until the log is 
successfully dumped and space becomes available. "
 
We have a threshold in place, that threshold procedure will call another 
store procedure that will email and page us when the tempdb is 70 % used.
We did not get any page from the system on the day when this problem 
occured.
 
So, On our recovery server, I tried to simulate this problem. I shrunk the 
tempdb size to 8 mb and put all the necessary thresholds in place and ran 
"update all statistics" for one of our big table. I was also monitoring the 
tempdb usage using a shell script, that will login every minute and log the 
tempdb usage.
 
What I found out was that, tempdb usage remained around 10%-15% for 35 mins 
and the next message I see suddenly is
 
" 00:00000:00001:2001/03/21 12:55:06.76 server  Space available in the log 
segment has fallen critically low in database 'tempdb'.  All future 
modifications to this database will be suspended until the log is 
successfully dumped and space becomes available. "
 
Again NO PAGE/NO E-MAIL. I cannot even run sp_who. This time I know my unix 
session (update statistics), so I went ahead and killed it. Then after few 
mins I received PAGE and E-MAIL. 
 
The problem I am facing is that, I am not able to monitor the space using 
the threshold and I know it only when tempdb has reached full usage. Is 
there any way that I can monitor tempdb usage effectively.
 
Can I use the dboption "Abort Tran on Log Full" ? Will this kill all the 
active transactions ? Can it be implemented on Production systems ? Please 
highlight pros/cons on this regard.
 
Thanks in advance.

Ethiraj
0
Ethiraj
3/23/2001 2:22:04 AM
sybase.sqlserver.performance 1181 articles. 0 followers. Follow

8 Replies
466 Views

Similar Articles

[PageSpeed] 14

What was the size of the tempdb database on the production system.  Is there any special 
configuration for that database?
-- 
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services

Get your free subscription to PowerTimes at http://www.powertimes.com
0
Jim
3/23/2001 4:58:01 AM
Thanks for the fast response.

Size of tempdb is 1004 mb. Our Sybase ASE is running on Sun Solaris - OS 
5.6, and this tempdb is in memory.

placement is like this

master - 2mb - data and log
master - 2mb - data and log
tempdb_mem01 - 500mb - data and log
tempdb_mem02 - 500mb - data and log

Only the select into/.... option is turned on.

Best regards,
Ethiraj
0
ETHIRAJ
3/23/2001 9:13:51 AM
A couple of things, in your test you run UPDATE ALL STATISTICS, is this what
the tempdb is filling up on in production?  Is running this type of UPDATE
STATISTICS indicated in some way, or are you just proactively seeking more
granular statistics on your tables. It is safe to say most folks *don't* run
this command as it is not typically needed.  I also think it could be useful
to see the sp_thresholdaction procedure from sybsystemprocs that is failing,
as well as the contents of systhresholds from tempdb *and* model.


George


<ETHIRAJ> wrote in message
news:C9FB96C0031490A40032B4CF85256A18.001F57A685256A18@webforums...
> Thanks for the fast response.
>
> Size of tempdb is 1004 mb. Our Sybase ASE is running on Sun Solaris - OS
> 5.6, and this tempdb is in memory.
>
> placement is like this
>
> master - 2mb - data and log
> master - 2mb - data and log
> tempdb_mem01 - 500mb - data and log
> tempdb_mem02 - 500mb - data and log
>
> Only the select into/.... option is turned on.
>
> Best regards,
> Ethiraj


0
George
3/23/2001 12:16:35 PM
I agree with George that running UPDATE ALL STATISTICS is in most cases overkill and a 
waste of resources.

Updating the statistics does use tempdb.  In my experience, if you have one process that 
uses up all the space in tempdb then most bets are off as far as thresholds and truncating 
the log go.  Lots of small transactions are OK and the system can manage that just fine.

How big is your big table?  If you run just UPDATE INDEX STATISTICS <tablename> do you 
still encounter the same problem?
-- 
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services

Get your free subscription to PowerTimes at http://www.powertimes.com
0
Jim
3/23/2001 2:55:39 PM
Between George and Jim I think I'll retire from here ;-)

Update stats will only use tempdb space for the sort and read of columns that are not the
major attribute of an index (leading column). I'm VERY HAPPY to see you guys explaining that
update all stats is, in the vast majority of cases, a was of time and precious I/O. Keep in
mind that update index stats will have to do a worktable sort and read for each minor
attribute of composite indexes and also has the potential to fill tempdb....but not as grate
as update all stats.

It's highly recommend that stats be added to inner cols of composite indexes and to
non-indexed join columns. But, only after full testing. There are times when more stats
reveals ugly distributions. In such cases it may be better to 'fool' the optimizer by
restricting the info it gets.

Now back to his problem at hand......

Later,

Eric

Jim Egan wrote:

> I agree with George that running UPDATE ALL STATISTICS is in most cases overkill and a
> waste of resources.
>
> Updating the statistics does use tempdb.  In my experience, if you have one process that
> uses up all the space in tempdb then most bets are off as far as thresholds and truncating
> the log go.  Lots of small transactions are OK and the system can manage that just fine.
>
> How big is your big table?  If you run just UPDATE INDEX STATISTICS <tablename> do you
> still encounter the same problem?
> --
> Jim Egan [TeamSybase]
> Senior Consultant
> Sybase Professional Services
>
> Get your free subscription to PowerTimes at http://www.powertimes.com

0
Eric
3/23/2001 5:39:20 PM
Actually the problem is not with Update Statistics. Some application login 
is eating my tempdb, and I was not notified via threshold. Since, I know 
Update Statistics will <ALL> option will shoot up tempb usage. So, I ran it 
to check my threshold procedure is firing or not.

How to overcome this problem, I like to be notified once certain amount of 
my tempdb is used.

Thanks for all your responses.

Cheers,
Ethiraj
0
ETHIRAJ
3/28/2001 7:07:11 AM
Are thresholds in tempdb reset on a server bounce? Do the thresholds need to be
set in model for this to be effective? If so, how does ASE cope with the
difference in size between tempdb and model?


Roger

ETHIRAJ wrote:

> Actually the problem is not with Update Statistics. Some application login
> is eating my tempdb, and I was not notified via threshold. Since, I know
> Update Statistics will <ALL> option will shoot up tempb usage. So, I ran it
> to check my threshold procedure is firing or not.
>
> How to overcome this problem, I like to be notified once certain amount of
> my tempdb is used.
>
> Thanks for all your responses.
>
> Cheers,
> Ethiraj

0
Roger
3/28/2001 1:24:35 PM
what I have done in the past (a workaround from tech support) is set the
threshold in tempdb and either insert or bulk copy into model.  You won't be
able to add it directly to model if there is a size difference.  We asked
for a percentage based threshold setting as an ER which would prevent this
kind of stuff, but no word.

George

Roger Broadbent <RBroadbent@wilco-int.com> wrote in message
news:3AC1E613.36C84BDB@wilco-int.com...
> Are thresholds in tempdb reset on a server bounce? Do the thresholds need
to be
> set in model for this to be effective? If so, how does ASE cope with the
> difference in size between tempdb and model?
>
>
> Roger
>
> ETHIRAJ wrote:
>
> > Actually the problem is not with Update Statistics. Some application
login
> > is eating my tempdb, and I was not notified via threshold. Since, I know
> > Update Statistics will <ALL> option will shoot up tempb usage. So, I ran
it
> > to check my threshold procedure is firing or not.
> >
> > How to overcome this problem, I like to be notified once certain amount
of
> > my tempdb is used.
> >
> > Thanks for all your responses.
> >
> > Cheers,
> > Ethiraj
>


0
George
3/28/2001 11:08:25 PM
Reply:

Similar Artilces:

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

tempdb performance
It's my understanding tempdb performs better on file system than on raws. Is this still the case? What unix parameters need to be set on the files system? Should 'allow sql server async i/o' bet set to 1, or does this only pertain to raws? Running ASE 12.0.0.6 EBF 10391 64bit on HP/UX 11.0. ...

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

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 performance enhancements
Hello , Kindly let me know your thoughts about the tempdb performance enhancements. Any thoughts most welcome. > Hello , > Kindly let me know your thoughts about the tempdb > performance enhancements. > > > Any thoughts most welcome. with many new modification to tempdb (read/google lazy writes), u can just create tempdb and forget about it(something i recall reading long time back in whitepapers). If you are running into tempdb realted issues then what issues (contentions, slowness in general or specific conditions involving tempdb, filling up, block...

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

checkpoint performance in tempdb
We have a bottleneck in tempdb in which we have the checkpoint process consuming a huge amount of CPU - so much that it slows everything down. This occurs when we run a large complex stored procedure. At the time this occurs, checkpoint is holding a page lock on page 24 of sysindexes (the DBINFO structure) in tempdb and it doesn't appear to let go for up to 1-2 hours. Is anyone aware of any checkpoint or tempdb bugs that involve a CPU bottleneck such as this? Adaptive Server Enterprise/12.0.0.3/P/SWR 9773 ESD 4/HP9000-879/HP-UX 11.0/1699/64bit/FBO/Thu Sep 6 07:23:46 2001 ...

tempdb full #3
I received this entry in the log: There is no space available in SYSLOGS to log a record for which space has been reserved. This process will retry at intervals of one minute. tempdb is full. So I did: dump tran tempdb with no_log go the execution of this command took a lot of time. So i interrupted it and did a restart. Is there a more efficient command to free the tempdb?? How to see which user/database filled up the tempdb?? thanks, > There is no space available in SYSLOGS to log a record for which space > has been reserved. This process will retry at inte...

tempdb performance #2
Hi, I have a 10G tempdb. For better tempdb performance, should I create tempdb on file system or raw device? Tempdb data (10G) is currently created on master device, while tempdb log (2G) is created on a separate disk. Is it better to have: 1. tempdb data + tempdb log on a separate disk or 2. tempdb data on separate disk, tempdb log on master or 3. tempdb data on master disk, tempdb log on separate disk Thanks Simon wrote: > I have a 10G tempdb. For better tempdb performance, should I create tempdb > on file system or raw device? What is it on now? >...

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

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

Web resources about - tempdb full - sybase.sqlserver.performance

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: 11/30/2015 6:09:47 AM