Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Tempdb is full

2 posts in General Discussion Last posting was on 2012-04-26 13:09:59.0Z
Raj Posted on 2012-04-26 00:16:51.0Z
Sender: 6ac7.4f989203.1804289383@sybase.com
From: Raj
Newsgroups: sybase.public.ase.general
Subject: Tempdb is full
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f9893f3.6b2c.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 25 Apr 2012 17:16:51 -0700
X-Trace: forums-1-dub 1335399411 172.20.134.41 (25 Apr 2012 17:16:51 -0700)
X-Original-Trace: 25 Apr 2012 17:16:51 -0700, 172.20.134.41
Lines: 168
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31054
Article PK: 73942

Hi,

User is getting below error. when I have run
sp_helpsegment 'default', It is 93% used. However I can't
see any tables using the tempdb. What could be the issue ??


Number = 1105, Severity = 17, State = 2, Line = 1, Message =
Can't allocate space for object 'temp worktable' in database
'tempdb' because 'system' segment is full/has no free
extents. If you ran out of space in syslogs, dump the
transaction log. Otherwise



sp_helpsegment 'default'
go

total_size total_pages free_pages
used_pages reserved_pages
----------------- --------------- ---------------
--------------- ---------------
15000.0MB 3840000 455058 3384942
0


1> sp_spaceused
2> go
database_name database_size
------------- -------------
tempdb 18006.0 MB

(1 row affected)
reserved data index_size unused
--------- --------- ---------- --------
663196 KB 641768 KB 3288 KB 18140 KB
(return status = 0)



1> sp_helpdb tempdb
2> go
name db_size owner dbid created status
------ ------------- ----- ---- ------------
--------------------------------------------------------------------
tempdb 18006.0 MB sa 2 Apr 11, 2012 select
into/bulkcopy/pllsort, trunc log on chkpt, mixed log and
data

(1 row affected)
name attribute_class attribute int_value char_value
comments
------ --------------- ------------- --------- ------------
--------
tempdb buffer manager cache binding 1 tempdb_cache
NULL
device_fragments size usage
created free kbytes
------------------------------ -------------
-------------------- -------------------------
----------------
master 6.0 MB data only
Mar 14 2012 4:43PM 2840
tempdbdev 10000.0 MB data and log
Mar 14 2012 4:44PM 1184388
TEMPDB_DATA02 5000.0 MB data only
Mar 16 2012 1:08PM 635768
TEMPDB_LOG01 3000.0 MB log only
Mar 16 2012 1:08PM not applicable


--------------------------------------------------------------
log only free kbytes = 4201200
device segment
------------- -----------------------------
TEMPDB_DATA02 default
TEMPDB_DATA02 system
TEMPDB_LOG01 logsegment
master -- unused by any segments --
tempdbdev default
tempdbdev logsegment
tempdbdev system



1> select @@version
2> goo


---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Adaptive Server Enterprise/15.0.3/EBF 17689 ESD#1.1
RELSE/P/RS6000/AIX 5.3/ase1503/2681/64-bit/FBO/Thu Aug 20
11:38:38 2009



And also top 30 tables(in KB)

TOP 30 tables

RESERVED_USED DATA_USED
INDEX_USED
------------------------------ ------------------
------------------ ------------------
xxxxx015915#M53DC171B_TMP 503476.00
503468.00 503468.00
xxxxx017491#M2FE51D43_TMP 64020.00
64004.00 64004.00
xxxxx01#2255#M730308CF_TMP 40012.00
40000.00 40000.00
xxxxx017491#Z0011D43_TMP 1336.00
1276.00 1276.00
xxxxx0121605#M00005465_TMP 576.00
552.00 552.00
syscolumns 504.00
364.00 364.00
sysprocedures 416.00
344.00 344.00
xxxxx017491#Z0131D43_TMP 344.00
280.00 280.00
syspartitions 312.00
236.00 236.00
xxxxx0114615#M4C783917_TMP 284.00
252.00 252.00
sysreferences 224.00
28.00 28.00
sysusers 224.00
28.00 28.00
sysattributes 192.00
24.00 24.00
sysjars 192.00
24.00 24.00
sysxtypes 192.00
24.00 24.00
systabstats 188.00
108.00 108.00
sysqueryplans 160.00
20.00 20.00
sysconstraints 156.00
20.00 20.00
sysobjects 156.00
88.00 88.00
sysindexes 152.00
104.00 104.00
systypes 128.00
20.00 20.00
xxxxx01#10366#Z0001_TMP 96.00
12.00 12.00
xxxxx01#10366#Z0002_TMP 96.00
12.00 12.00
xxxxx01#10575#Z0001_TMP 96.00
12.00 12.00
xxxxx01#10575#Z0004_TMP 96.00
12.00 12.00
xxxxx01#10575#Z0011_TMP 96.00
12.00 12.00
xxxxx01#11150#Z0002_TMP 96.00
12.00 12.00
xxxxx01#11422#Z0002_TMP 96.00
12.00 12.00
xxxxx01#1164#Z0002_TMP 96.00
12.00 12.00
xxxxx01#12209#Z0001_TMP 96.00
12.00 12.00


Thanks,
Raj


"Mark A. Parsons" <iron_horse Posted on 2012-04-26 13:09:59.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Tempdb is full
References: <4f9893f3.6b2c.1681692777@sybase.com>
In-Reply-To: <4f9893f3.6b2c.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f994927$1@forums-1-dub>
Date: 26 Apr 2012 06:09:59 -0700
X-Trace: forums-1-dub 1335445799 10.22.241.152 (26 Apr 2012 06:09:59 -0700)
X-Original-Trace: 26 Apr 2012 06:09:59 -0700, vip152.sybase.com
Lines: 183
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31055
Article PK: 73949

One small detail ... the error message mentions the 'system' segment so you'd want to run 'sp_helpsegment "system"'.
(Though based on your database configuration I'd expect you'll find the 'system' segment has the same space usage as the
'default' segment.)

As for what's using up all the space? tempdb is used for several purposes including storing #temp tables, storing
worktables (ie, intermediate result sets used for currently running queries), data sets that are currently being sorted
(eg, worktables, update stats on non-leading columns of indexes), etc.

You may be able to find the space hogs with the pssinfo(<spid>,'tempdb_pages') function call. Alternatively, take a
look at currently active spids (eg, look at master..monProcessObject and master..monProcessSQLText) as a starter. If
someone is running 'update index stats' on a really large table with lots of non-leading index columns ... this could be
a big space user. If you have some spids running queries, grab their query plan (sp_showplan) to see if they have a
poor query plan (also match with what you see in monProcessObject).

On 04/25/2012 18:16, Raj wrote:
> Hi,
>
> User is getting below error. when I have run
> sp_helpsegment 'default', It is 93% used. However I can't
> see any tables using the tempdb. What could be the issue ??
>
>
> Number = 1105, Severity = 17, State = 2, Line = 1, Message =
> Can't allocate space for object 'temp worktable' in database
> 'tempdb' because 'system' segment is full/has no free
> extents. If you ran out of space in syslogs, dump the
> transaction log. Otherwise
>
>
>
> sp_helpsegment 'default'
> go
>
> total_size total_pages free_pages
> used_pages reserved_pages
> ----------------- --------------- ---------------
> --------------- ---------------
> 15000.0MB 3840000 455058 3384942
> 0
>
>
> 1> sp_spaceused
> 2> go
> database_name database_size
> ------------- -------------
> tempdb 18006.0 MB
>
> (1 row affected)
> reserved data index_size unused
> --------- --------- ---------- --------
> 663196 KB 641768 KB 3288 KB 18140 KB
> (return status = 0)
>
>
>
> 1> sp_helpdb tempdb
> 2> go
> name db_size owner dbid created status
> ------ ------------- ----- ---- ------------
> --------------------------------------------------------------------
> tempdb 18006.0 MB sa 2 Apr 11, 2012 select
> into/bulkcopy/pllsort, trunc log on chkpt, mixed log and
> data
>
> (1 row affected)
> name attribute_class attribute int_value char_value
> comments
> ------ --------------- ------------- --------- ------------
> --------
> tempdb buffer manager cache binding 1 tempdb_cache
> NULL
> device_fragments size usage
> created free kbytes
> ------------------------------ -------------
> -------------------- -------------------------
> ----------------
> master 6.0 MB data only
> Mar 14 2012 4:43PM 2840
> tempdbdev 10000.0 MB data and log
> Mar 14 2012 4:44PM 1184388
> TEMPDB_DATA02 5000.0 MB data only
> Mar 16 2012 1:08PM 635768
> TEMPDB_LOG01 3000.0 MB log only
> Mar 16 2012 1:08PM not applicable
>
>
> --------------------------------------------------------------
> log only free kbytes = 4201200
> device segment
> ------------- -----------------------------
> TEMPDB_DATA02 default
> TEMPDB_DATA02 system
> TEMPDB_LOG01 logsegment
> master -- unused by any segments --
> tempdbdev default
> tempdbdev logsegment
> tempdbdev system
>
>
>
> 1> select @@version
> 2> goo
>
>
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Adaptive Server Enterprise/15.0.3/EBF 17689 ESD#1.1
> RELSE/P/RS6000/AIX 5.3/ase1503/2681/64-bit/FBO/Thu Aug 20
> 11:38:38 2009
>
>
>
> And also top 30 tables(in KB)
>
> TOP 30 tables
>
> RESERVED_USED DATA_USED
> INDEX_USED
> ------------------------------ ------------------
> ------------------ ------------------
> xxxxx015915#M53DC171B_TMP 503476.00
> 503468.00 503468.00
> xxxxx017491#M2FE51D43_TMP 64020.00
> 64004.00 64004.00
> xxxxx01#2255#M730308CF_TMP 40012.00
> 40000.00 40000.00
> xxxxx017491#Z0011D43_TMP 1336.00
> 1276.00 1276.00
> xxxxx0121605#M00005465_TMP 576.00
> 552.00 552.00
> syscolumns 504.00
> 364.00 364.00
> sysprocedures 416.00
> 344.00 344.00
> xxxxx017491#Z0131D43_TMP 344.00
> 280.00 280.00
> syspartitions 312.00
> 236.00 236.00
> xxxxx0114615#M4C783917_TMP 284.00
> 252.00 252.00
> sysreferences 224.00
> 28.00 28.00
> sysusers 224.00
> 28.00 28.00
> sysattributes 192.00
> 24.00 24.00
> sysjars 192.00
> 24.00 24.00
> sysxtypes 192.00
> 24.00 24.00
> systabstats 188.00
> 108.00 108.00
> sysqueryplans 160.00
> 20.00 20.00
> sysconstraints 156.00
> 20.00 20.00
> sysobjects 156.00
> 88.00 88.00
> sysindexes 152.00
> 104.00 104.00
> systypes 128.00
> 20.00 20.00
> xxxxx01#10366#Z0001_TMP 96.00
> 12.00 12.00
> xxxxx01#10366#Z0002_TMP 96.00
> 12.00 12.00
> xxxxx01#10575#Z0001_TMP 96.00
> 12.00 12.00
> xxxxx01#10575#Z0004_TMP 96.00
> 12.00 12.00
> xxxxx01#10575#Z0011_TMP 96.00
> 12.00 12.00
> xxxxx01#11150#Z0002_TMP 96.00
> 12.00 12.00
> xxxxx01#11422#Z0002_TMP 96.00
> 12.00 12.00
> xxxxx01#1164#Z0002_TMP 96.00
> 12.00 12.00
> xxxxx01#12209#Z0001_TMP 96.00
> 12.00 12.00
>
>
> Thanks,
> Raj