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.

Abort tran on log full for tempdb

5 posts in General Discussion Last posting was on 2012-11-27 18:52:41.0Z
dba1 Posted on 2012-11-26 23:00:16.0Z
Sender: 4914.50b37def.1804289383@sybase.com
From: dba1
Newsgroups: sybase.public.ase.general
Subject: Abort tran on log full for tempdb
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50b3f480.686e.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 26 Nov 2012 15:00:16 -0800
X-Trace: forums-1-dub 1353970816 172.20.134.41 (26 Nov 2012 15:00:16 -0800)
X-Original-Trace: 26 Nov 2012 15:00:16 -0800, 172.20.134.41
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31534
Article PK: 74423

Why would a tempdb not clear even with abort tran on log
full setting?

Segment Segment Name Size MB Used MB %
Full
------- ------------------------------ ------- --------
-------
0 system 4000 33.375
0.834
1 default 4000 11.225
0.281
2 logsegment 4000 3676.400
91.910
========
3721.000

name db_size owner dbid created status
------- ------------- ----- ---- ------------
------------------------------------------------------------------------------------------------------------------
tempdb_dev 4000.0 MB sa 6 Nov 24, 2012 select
into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log
full, mixed log and data, user created temp db


"Mark A. Parsons" <iron_horse Posted on 2012-11-27 00:35:51.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.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: Abort tran on log full for tempdb
References: <50b3f480.686e.1681692777@sybase.com>
In-Reply-To: <50b3f480.686e.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 121124-1, 11/24/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50b40ae7$1@forums-1-dub>
Date: 26 Nov 2012 16:35:51 -0800
X-Trace: forums-1-dub 1353976551 172.20.134.152 (26 Nov 2012 16:35:51 -0800)
X-Original-Trace: 26 Nov 2012 16:35:51 -0800, vip152.sybase.com
Lines: 38
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31536
Article PK: 74425

Not enough info to say for sure what the issue is ...

- where did you get your measurements from? if a home-grown query, have you verified that the query is providing
correct results? (eg, what does 'tempdb..sp_helpsegment logsegment' show you?)

- have you verified that you do not have an open transaction in tempdb that's keeping the log from being truncated
(check master..syslogshold and master..systransactions)

- does 'dump tran/truncate_only' clear the log or does the log remain full?

- what is the layout of the database in terms of devices and segments (tempdb..sp_helpdb tempdb)?

- do you have any permanent/temporary tables taking up a lot of space? wondering if a recent manipulation of the
segments (eg, sp_dropsegment) could have left the tables' data residing on the device where the logsegment now resides?

On 11/26/2012 16:00, dba1 wrote:
> Why would a tempdb not clear even with abort tran on log
> full setting?
>
> Segment Segment Name Size MB Used MB %
> Full
> ------- ------------------------------ ------- --------
> -------
> 0 system 4000 33.375
> 0.834
> 1 default 4000 11.225
> 0.281
> 2 logsegment 4000 3676.400
> 91.910
> ========
> 3721.000
>
> name db_size owner dbid created status
> ------- ------------- ----- ---- ------------
> ------------------------------------------------------------------------------------------------------------------
> tempdb_dev 4000.0 MB sa 6 Nov 24, 2012 select
> into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log
> full, mixed log and data, user created temp db


dba1 Posted on 2012-11-27 08:49:57.0Z
Sender: f73.50b47b2a.1804289383@sybase.com
From: dba1
Newsgroups: sybase.public.ase.general
Subject: Re: Abort tran on log full for tempdb
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50b47eb5.107a.1681692777@sybase.com>
References: <50b40ae7$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 27 Nov 2012 00:49:57 -0800
X-Trace: forums-1-dub 1354006197 172.20.134.41 (27 Nov 2012 00:49:57 -0800)
X-Original-Trace: 27 Nov 2012 00:49:57 -0800, 172.20.134.41
Lines: 66
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31538
Article PK: 74428

Mark

The proc is homegrown and looks at sysusages and
syssegments. Its accurate measure of logusage.

There is an open tran against the tempdb. But having the
"abort tran on log full" option, I would have expected this
tran to have been termined. Why would that not have been the
case?

The 4Gb tempdb is spread across 2 filesystem devices with
mixed data and log

No other tables hogging space outside of the offending
processes

> Not enough info to say for sure what the issue is ...
>
> - where did you get your measurements from? if a
> home-grown query, have you verified that the query is
> providing correct results? (eg, what does
> 'tempdb..sp_helpsegment logsegment' show you?)
>
> - have you verified that you do not have an open
> transaction in tempdb that's keeping the log from being
> truncated (check master..syslogshold and
> master..systransactions)
>
> - does 'dump tran/truncate_only' clear the log or does the
> log remain full?
>
> - what is the layout of the database in terms of devices
> and segments (tempdb..sp_helpdb tempdb)?
>
> - do you have any permanent/temporary tables taking up a
> lot of space? wondering if a recent manipulation of the
> segments (eg, sp_dropsegment) could have left the tables'
> data residing on the device where the logsegment now
> resides?
>
> On 11/26/2012 16:00, dba1 wrote:
> > Why would a tempdb not clear even with abort tran on log
> > full setting?
> >
> > Segment Segment Name Size MB Used MB
> > % Full
> > ------- ------------------------------ ------- --------
> > -------
> > 0 system 4000 33.375
> > 0.834
> > 1 default 4000 11.225
> > 0.281
> > 2 logsegment 4000 3676.400
> > 91.910
> > ========
> > 3721.000
> >
> > name db_size owner dbid created status
> > ------- ------------- ----- ---- ------------
> >
> ----------------------------------------------------------
> >
> --------------------------------------------------------
> > tempdb_dev 4000.0 MB sa 6 Nov 24, 2012 select
> into/bulkcopy/pllsort, trunc log on chkpt, abort tran on
> > log full, mixed log and data, user created temp db


"Mark A. Parsons" <iron_horse Posted on 2012-11-27 13:43:50.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.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: Abort tran on log full for tempdb
References: <50b40ae7$1@forums-1-dub> <50b47eb5.107a.1681692777@sybase.com>
In-Reply-To: <50b47eb5.107a.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 121124-1, 11/24/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50b4c396$1@forums-1-dub>
Date: 27 Nov 2012 05:43:50 -0800
X-Trace: forums-1-dub 1354023830 172.20.134.152 (27 Nov 2012 05:43:50 -0800)
X-Original-Trace: 27 Nov 2012 05:43:50 -0800, vip152.sybase.com
Lines: 69
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31539
Article PK: 74427

According to the numbers you've posted the log is not full, so 'abort tran on log full' hasn't had a reason to fire, yet.

On 11/27/2012 01:49, dba1 wrote:
> Mark
>
> The proc is homegrown and looks at sysusages and
> syssegments. Its accurate measure of logusage.
>
> There is an open tran against the tempdb. But having the
> "abort tran on log full" option, I would have expected this
> tran to have been termined. Why would that not have been the
> case?
>
> The 4Gb tempdb is spread across 2 filesystem devices with
> mixed data and log
>
> No other tables hogging space outside of the offending
> processes
>
>> Not enough info to say for sure what the issue is ...
>>
>> - where did you get your measurements from? if a
>> home-grown query, have you verified that the query is
>> providing correct results? (eg, what does
>> 'tempdb..sp_helpsegment logsegment' show you?)
>>
>> - have you verified that you do not have an open
>> transaction in tempdb that's keeping the log from being
>> truncated (check master..syslogshold and
>> master..systransactions)
>>
>> - does 'dump tran/truncate_only' clear the log or does the
>> log remain full?
>>
>> - what is the layout of the database in terms of devices
>> and segments (tempdb..sp_helpdb tempdb)?
>>
>> - do you have any permanent/temporary tables taking up a
>> lot of space? wondering if a recent manipulation of the
>> segments (eg, sp_dropsegment) could have left the tables'
>> data residing on the device where the logsegment now
>> resides?
>>
>> On 11/26/2012 16:00, dba1 wrote:
>>> Why would a tempdb not clear even with abort tran on log
>>> full setting?
>>>
>>> Segment Segment Name Size MB Used MB
>>> % Full
>>> ------- ------------------------------ ------- --------
>>> -------
>>> 0 system 4000 33.375
>>> 0.834
>>> 1 default 4000 11.225
>>> 0.281
>>> 2 logsegment 4000 3676.400
>>> 91.910
>>> ========
>>> 3721.000
>>>
>>> name db_size owner dbid created status
>>> ------- ------------- ----- ---- ------------
>>>
>> ----------------------------------------------------------
>>>
>> --------------------------------------------------------
>>> tempdb_dev 4000.0 MB sa 6 Nov 24, 2012 select
>> into/bulkcopy/pllsort, trunc log on chkpt, abort tran on
>>> log full, mixed log and data, user created temp db


dba1 Posted on 2012-11-27 18:52:41.0Z
Sender: 1397.50b48a7a.1804289383@sybase.com
From: dba1
Newsgroups: sybase.public.ase.general
Subject: Re: Abort tran on log full for tempdb
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50b50bf9.280d.1681692777@sybase.com>
References: <50b4c396$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 27 Nov 2012 10:52:41 -0800
X-Trace: forums-1-dub 1354042361 172.20.134.41 (27 Nov 2012 10:52:41 -0800)
X-Original-Trace: 27 Nov 2012 10:52:41 -0800, 172.20.134.41
Lines: 96
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31542
Article PK: 74431

tempdb log was full and we were unable to process against
this tempdb with multitude of messages as below on errorlog
until process finally was manually killed. So question is
why was "abort tran on log full" not effective even if the
home grown proc did not capture logsegment details
correctly.

server background task message: ERROR: database LOG FULL
server Error: 1105, Severity: 17, State: 4
server Can't allocate space for object 'syslogs' in
database 'tempdb' because 'logsegment' segment is full/has
no free extents. If you ran out of space in syslogs, dump
the transaction log. Otherwise, use ALTER DATABASE to
increase the size of the segment.
rror: 1105, Severity: 17, State: 4
server Can't allocate space for object 'syslogs' in
database 'tempdb' because 'logsegment' segment is full/has
no free extents. If you ran out of space in syslogs, dump
the transaction log. Otherwise, use ALTER DATABASE to
increase the size of the segment.

> According to the numbers you've posted the log is not full
> , so 'abort tran on log full' hasn't had a reason to fire,
> yet.
>
> On 11/27/2012 01:49, dba1 wrote:
> > Mark
> >
> > The proc is homegrown and looks at sysusages and
> > syssegments. Its accurate measure of logusage.
> >
> > There is an open tran against the tempdb. But having the
> > "abort tran on log full" option, I would have expected
> > this tran to have been termined. Why would that not have
> > been the case?
> >
> > The 4Gb tempdb is spread across 2 filesystem devices
> > with mixed data and log
> >
> > No other tables hogging space outside of the offending
> > processes
> >
> >> Not enough info to say for sure what the issue is ...
> >>
> >> - where did you get your measurements from? if a
> >> home-grown query, have you verified that the query is
> >> providing correct results? (eg, what does
> >> 'tempdb..sp_helpsegment logsegment' show you?)
> >>
> >> - have you verified that you do not have an open
> >> transaction in tempdb that's keeping the log from being
> >> truncated (check master..syslogshold and
> >> master..systransactions)
> >>
> >> - does 'dump tran/truncate_only' clear the log or does
> the >> log remain full?
> >>
> >> - what is the layout of the database in terms of
> devices >> and segments (tempdb..sp_helpdb tempdb)?
> >>
> >> - do you have any permanent/temporary tables taking up
> a >> lot of space? wondering if a recent manipulation of
> the >> segments (eg, sp_dropsegment) could have left the
> tables' >> data residing on the device where the
> logsegment now >> resides?
> >>
> >> On 11/26/2012 16:00, dba1 wrote:
> >>> Why would a tempdb not clear even with abort tran on
> log >>> full setting?
> >>>
> >>> Segment Segment Name Size MB Used
> MB >>> % Full
> >>> ------- ------------------------------ -------
> -------- >>> -------
> >>> 0 system 4000
> 33.375 >>> 0.834
> >>> 1 default 4000
> 11.225 >>> 0.281
> >>> 2 logsegment 4000
> 3676.400 >>> 91.910
> >>>
> ======== >>>
> 3721.000 >>>
> >>> name db_size owner dbid created status
> >>> ------- ------------- ----- ---- ------------
> >>>
> >>
> ----------------------------------------------------------
> >>> >>
> --------------------------------------------------------
> >>> tempdb_dev 4000.0 MB sa 6 Nov 24, 2012
> select >> into/bulkcopy/pllsort, trunc log on chkpt, abort
> tran on >>> log full, mixed log and data, user created
> temp db