I'm on ASE 15.0.2. My tempdb sometimes fills with the error message "Can't
allocate space for object 'syslogs' in database 'tempdb' because
'logsegment' segment is full/has no free extents." And that causes the
database to effectively hang as everyone locks up in a cascading fashion. I
thought I was solving this by turning on "abort tran on log full" in tempdb.
I thought that the process receiving the above error would automatically
abort instead. I thought it would be better to sacrifice one process in
order so that the entire database doesn't hang.

But, in practice this technique seems not to work, as I still occasionally
see the "log full" error. I was told by Sybase personnel that the reason for
this is because my tempdb is a mixed data+log database. In that scenario,
the "abort tran on log full" option has no effect. This was news to me; I
haven't heard anything like that before.

What does anyone here think about that claim? By the way, I am aware there
are several other techniques to solve my tempdb problem, but there's no real
need to discuss those right now. I'm really just wondering about the "abort
tran on log full" and why it doesn't seem to work.

For what it's worth John, I would expect "abort tran on log full" to behave
the way you expect it to as well. In fact, here is what I observe on my
server which is what we expect:

1> select @@version
2> go

Adaptive Server Enterprise/15.0.2/EBF 15654 ESD#4/P/Linux Intel/Linux
2.4.21-47.ELsmp i686/ase1502/2528/32-bit/FBO/Sat Apr 5 05:42 2008

1> sp_helpdb tempdb
2> go
name db_size owner dbid created
status
------------ -------------------------- ---------- -------- ------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tempdb 4.0 MB sa 2 Sep 08, 2008
select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log
full, mixed log and data

(1 row affected)
device_fragments size
usage
created free kbytes
------------------------------------------------------------ --------------------------
----------------------------------------
-------------------------------------------------- --------------------------------
master 4.0 MB
data and log
Sep 8 2008 2:39PM 2200
(return status = 0)
1> begin tran
2> go
1> insert into tempdb..testtab
2> select number,str(number,12,0)
3> from master..spt_values
4> go

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 successfully dumped and space becomes available.
Msg 1105, Level 17, State 4:
Server 'ROCK', Line 1:
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.
1> select @@trancount
2> go

-----------
0

(1 row affected)

1> select * from master..syslogshold
2> go
dbid reserved spid page xactid masterxactid
starttime
name
xloid
------ ----------- ------ ----------- -------------- -------------- --------------------------
--------------------------------------------------------------------------------------------------------------------------------------
-----------

(0 rows affected)




"John Flynn" <jflynn (AT) miqs (DOT) com> wrote

For what it's worth John, I would expect "abort tran on log full" to behave
the way you expect it to as well. In fact, here is what I observe on my
server which is what we expect:

1> select @@version
2> go

Adaptive Server Enterprise/15.0.2/EBF 15654 ESD#4/P/Linux Intel/Linux
2.4.21-47.ELsmp i686/ase1502/2528/32-bit/FBO/Sat Apr 5 05:42 2008

1> sp_helpdb tempdb
2> go
name db_size owner dbid created
status
------------ -------------------------- ---------- -------- ------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tempdb 4.0 MB sa 2 Sep 08, 2008
select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log
full, mixed log and data

(1 row affected)
device_fragments size
usage
created free kbytes
------------------------------------------------------------ --------------------------
----------------------------------------
-------------------------------------------------- --------------------------------
master 4.0 MB
data and log
Sep 8 2008 2:39PM 2200
(return status = 0)
1> begin tran
2> go
1> insert into tempdb..testtab
2> select number,str(number,12,0)
3> from master..spt_values
4> go

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 successfully dumped and space becomes available.
Msg 1105, Level 17, State 4:
Server 'ROCK', Line 1:
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.
1> select @@trancount
2> go

-----------
0

(1 row affected)

1> select * from master..syslogshold
2> go
dbid reserved spid page xactid masterxactid
starttime
name
xloid
------ ----------- ------ ----------- -------------- -------------- --------------------------
--------------------------------------------------------------------------------------------------------------------------------------
-----------

(0 rows affected)




"John Flynn" <jflynn (AT) miqs (DOT) com> wrote

For what it's worth John, I would expect "abort tran on log full" to behave
the way you expect it to as well. In fact, here is what I observe on my
server which is what we expect:

1> select @@version
2> go

Adaptive Server Enterprise/15.0.2/EBF 15654 ESD#4/P/Linux Intel/Linux
2.4.21-47.ELsmp i686/ase1502/2528/32-bit/FBO/Sat Apr 5 05:42 2008

1> sp_helpdb tempdb
2> go
name db_size owner dbid created
status
------------ -------------------------- ---------- -------- ------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tempdb 4.0 MB sa 2 Sep 08, 2008
select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log
full, mixed log and data

(1 row affected)
device_fragments size
usage
created free kbytes
------------------------------------------------------------ --------------------------
----------------------------------------
-------------------------------------------------- --------------------------------
master 4.0 MB
data and log
Sep 8 2008 2:39PM 2200
(return status = 0)
1> begin tran
2> go
1> insert into tempdb..testtab
2> select number,str(number,12,0)
3> from master..spt_values
4> go

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 successfully dumped and space becomes available.
Msg 1105, Level 17, State 4:
Server 'ROCK', Line 1:
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.
1> select @@trancount
2> go

-----------
0

(1 row affected)

1> select * from master..syslogshold
2> go
dbid reserved spid page xactid masterxactid
starttime
name
xloid
------ ----------- ------ ----------- -------------- -------------- --------------------------
--------------------------------------------------------------------------------------------------------------------------------------
-----------

(0 rows affected)




"John Flynn" <jflynn (AT) miqs (DOT) com> wrote

For what it's worth John, I would expect "abort tran on log full" to behave
the way you expect it to as well. In fact, here is what I observe on my
server which is what we expect:

1> select @@version
2> go

Adaptive Server Enterprise/15.0.2/EBF 15654 ESD#4/P/Linux Intel/Linux
2.4.21-47.ELsmp i686/ase1502/2528/32-bit/FBO/Sat Apr 5 05:42 2008

1> sp_helpdb tempdb
2> go
name db_size owner dbid created
status
------------ -------------------------- ---------- -------- ------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tempdb 4.0 MB sa 2 Sep 08, 2008
select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log
full, mixed log and data

(1 row affected)
device_fragments size
usage
created free kbytes
------------------------------------------------------------ --------------------------
----------------------------------------
-------------------------------------------------- --------------------------------
master 4.0 MB
data and log
Sep 8 2008 2:39PM 2200
(return status = 0)
1> begin tran
2> go
1> insert into tempdb..testtab
2> select number,str(number,12,0)
3> from master..spt_values
4> go

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 successfully dumped and space becomes available.
Msg 1105, Level 17, State 4:
Server 'ROCK', Line 1:
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.
1> select @@trancount
2> go

-----------
0

(1 row affected)

1> select * from master..syslogshold
2> go
dbid reserved spid page xactid masterxactid
starttime
name
xloid
------ ----------- ------ ----------- -------------- -------------- --------------------------
--------------------------------------------------------------------------------------------------------------------------------------
-----------

(0 rows affected)




"John Flynn" <jflynn (AT) miqs (DOT) com> wrote

For what it's worth John, I would expect "abort tran on log full" to behave
the way you expect it to as well. In fact, here is what I observe on my
server which is what we expect:

1> select @@version
2> go

Adaptive Server Enterprise/15.0.2/EBF 15654 ESD#4/P/Linux Intel/Linux
2.4.21-47.ELsmp i686/ase1502/2528/32-bit/FBO/Sat Apr 5 05:42 2008

1> sp_helpdb tempdb
2> go
name db_size owner dbid created
status
------------ -------------------------- ---------- -------- ------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tempdb 4.0 MB sa 2 Sep 08, 2008
select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log
full, mixed log and data

(1 row affected)
device_fragments size
usage
created free kbytes
------------------------------------------------------------ --------------------------
----------------------------------------
-------------------------------------------------- --------------------------------
master 4.0 MB
data and log
Sep 8 2008 2:39PM 2200
(return status = 0)
1> begin tran
2> go
1> insert into tempdb..testtab
2> select number,str(number,12,0)
3> from master..spt_values
4> go

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 successfully dumped and space becomes available.
Msg 1105, Level 17, State 4:
Server 'ROCK', Line 1:
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.
1> select @@trancount
2> go

-----------
0

(1 row affected)

1> select * from master..syslogshold
2> go
dbid reserved spid page xactid masterxactid
starttime
name
xloid
------ ----------- ------ ----------- -------------- -------------- --------------------------
--------------------------------------------------------------------------------------------------------------------------------------
-----------

(0 rows affected)




"John Flynn" <jflynn (AT) miqs (DOT) com> wrote

For what it's worth John, I would expect "abort tran on log full" to behave
the way you expect it to as well. In fact, here is what I observe on my
server which is what we expect:

1> select @@version
2> go

Adaptive Server Enterprise/15.0.2/EBF 15654 ESD#4/P/Linux Intel/Linux
2.4.21-47.ELsmp i686/ase1502/2528/32-bit/FBO/Sat Apr 5 05:42 2008

1> sp_helpdb tempdb
2> go
name db_size owner dbid created
status
------------ -------------------------- ---------- -------- ------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tempdb 4.0 MB sa 2 Sep 08, 2008
select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log
full, mixed log and data

(1 row affected)
device_fragments size
usage
created free kbytes
------------------------------------------------------------ --------------------------
----------------------------------------
-------------------------------------------------- --------------------------------
master 4.0 MB
data and log
Sep 8 2008 2:39PM 2200
(return status = 0)
1> begin tran
2> go
1> insert into tempdb..testtab
2> select number,str(number,12,0)
3> from master..spt_values
4> go

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 successfully dumped and space becomes available.
Msg 1105, Level 17, State 4:
Server 'ROCK', Line 1:
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.
1> select @@trancount
2> go

-----------
0

(1 row affected)

1> select * from master..syslogshold
2> go
dbid reserved spid page xactid masterxactid
starttime
name
xloid
------ ----------- ------ ----------- -------------- -------------- --------------------------
--------------------------------------------------------------------------------------------------------------------------------------
-----------

(0 rows affected)




"John Flynn" <jflynn (AT) miqs (DOT) com> wrote

For what it's worth John, I would expect "abort tran on log full" to behave
the way you expect it to as well. In fact, here is what I observe on my
server which is what we expect:

1> select @@version
2> go

Adaptive Server Enterprise/15.0.2/EBF 15654 ESD#4/P/Linux Intel/Linux
2.4.21-47.ELsmp i686/ase1502/2528/32-bit/FBO/Sat Apr 5 05:42 2008

1> sp_helpdb tempdb
2> go
name db_size owner dbid created
status
------------ -------------------------- ---------- -------- ------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tempdb 4.0 MB sa 2 Sep 08, 2008
select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log
full, mixed log and data

(1 row affected)
device_fragments size
usage
created free kbytes
------------------------------------------------------------ --------------------------
----------------------------------------
-------------------------------------------------- --------------------------------
master 4.0 MB
data and log
Sep 8 2008 2:39PM 2200
(return status = 0)
1> begin tran
2> go
1> insert into tempdb..testtab
2> select number,str(number,12,0)
3> from master..spt_values
4> go

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 successfully dumped and space becomes available.
Msg 1105, Level 17, State 4:
Server 'ROCK', Line 1:
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.
1> select @@trancount
2> go

-----------
0

(1 row affected)

1> select * from master..syslogshold
2> go
dbid reserved spid page xactid masterxactid
starttime
name
xloid
------ ----------- ------ ----------- -------------- -------------- --------------------------
--------------------------------------------------------------------------------------------------------------------------------------
-----------

(0 rows affected)




"John Flynn" <jflynn (AT) miqs (DOT) com> wrote

For what it's worth John, I would expect "abort tran on log full" to behave
the way you expect it to as well. In fact, here is what I observe on my
server which is what we expect:

1> select @@version
2> go

Adaptive Server Enterprise/15.0.2/EBF 15654 ESD#4/P/Linux Intel/Linux
2.4.21-47.ELsmp i686/ase1502/2528/32-bit/FBO/Sat Apr 5 05:42 2008

1> sp_helpdb tempdb
2> go
name db_size owner dbid created
status
------------ -------------------------- ---------- -------- ------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tempdb 4.0 MB sa 2 Sep 08, 2008
select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log
full, mixed log and data

(1 row affected)
device_fragments size
usage
created free kbytes
------------------------------------------------------------ --------------------------
----------------------------------------
-------------------------------------------------- --------------------------------
master 4.0 MB
data and log
Sep 8 2008 2:39PM 2200
(return status = 0)
1> begin tran
2> go
1> insert into tempdb..testtab
2> select number,str(number,12,0)
3> from master..spt_values
4> go

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 successfully dumped and space becomes available.
Msg 1105, Level 17, State 4:
Server 'ROCK', Line 1:
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.
1> select @@trancount
2> go

-----------
0

(1 row affected)

1> select * from master..syslogshold
2> go
dbid reserved spid page xactid masterxactid
starttime
name
xloid
------ ----------- ------ ----------- -------------- -------------- --------------------------
--------------------------------------------------------------------------------------------------------------------------------------
-----------

(0 rows affected)




"John Flynn" <jflynn (AT) miqs (DOT) com> wrote

For what it's worth John, I would expect "abort tran on log full" to behave
the way you expect it to as well. In fact, here is what I observe on my
server which is what we expect:

1> select @@version
2> go

Adaptive Server Enterprise/15.0.2/EBF 15654 ESD#4/P/Linux Intel/Linux
2.4.21-47.ELsmp i686/ase1502/2528/32-bit/FBO/Sat Apr 5 05:42 2008

1> sp_helpdb tempdb
2> go
name db_size owner dbid created
status
------------ -------------------------- ---------- -------- ------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tempdb 4.0 MB sa 2 Sep 08, 2008
select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log
full, mixed log and data

(1 row affected)
device_fragments size
usage
created free kbytes
------------------------------------------------------------ --------------------------
----------------------------------------
-------------------------------------------------- --------------------------------
master 4.0 MB
data and log
Sep 8 2008 2:39PM 2200
(return status = 0)
1> begin tran
2> go
1> insert into tempdb..testtab
2> select number,str(number,12,0)
3> from master..spt_values
4> go

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 successfully dumped and space becomes available.
Msg 1105, Level 17, State 4:
Server 'ROCK', Line 1:
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.
1> select @@trancount
2> go

-----------
0

(1 row affected)

1> select * from master..syslogshold
2> go
dbid reserved spid page xactid masterxactid
starttime
name
xloid
------ ----------- ------ ----------- -------------- -------------- --------------------------
--------------------------------------------------------------------------------------------------------------------------------------
-----------

(0 rows affected)




"John Flynn" <jflynn (AT) miqs (DOT) com> wrote