SAP Sybase ASE 15.7 tempdb full no space left lct_admin abort




tempdb is full - what to do now ?


One of the worst things about tempdb being full is that it can prevent both users and administrators from using the system.

If users and administrators are using the same tempdb, commands such as sp_who don't work for the sa leaving people wondering what to do next.


Errors when tempdb is full


As the sa:
Failed to allocate disk space for a work table in database 'tempdb'.  You may be able to free up space by using the DUMP TRANsaction command, or you may want to extend the size of the database by using the ALTER DATABASE command.



sp_who shows:
The transaction log in database tempdb is almost full.  Your transaction is being suspended until space is made available in the log.



select * from syslogshold then shows:
Failed to allocate disk space for a work table in database 'tempdb'.  You may be able to free up space by using the DUMP TRANsaction command, or you may want to extend the size of the database by using the ALTER DATABASE command.


Use a query that works when tempdb is full


select "spid=" + convert( varchar(3), SPID) + " login=" + suser_name(ServerUserID) + " SQLText=" + SQLText from master..monProcessSQLText

1> select "spid=" + convert( varchar(3), SPID) + "  login=" + suser_name(ServerUserID) + "  SQLText=" + SQLText from master..monProcessSQLText
2> go
                                                                                                                                                                                                                                                                                                                                                            
 ---------------------------------------------------------------------------------------------------------
 spid=16  login=sa  SQLText=sp_who                                                                                                                                                                                                                                                                                                                          
 spid=17  login=fsmith  SQLText=insert junk select * from raw_test..junk                                                                                                                                                                                                                                                                                     
 spid=20  login=sa  SQLText=select "spid=" + convert( varchar(3), SPID) + "  login=" + suser_name(ServerUserID) + "  SQLText=" + SQLText from master..monProcessSQLText                                                                                                                                                                                     
(3 rows affected)
1>


Option 1 kill offending spid


In the test example above we can identify that spid 17 has the offending query.

kill 17
go


During a simple test it took over 4 minutes for the tempdb to become free so a degree of patience is needed.



Option 2 kill off all spids using tempdb


If it was impossible to determine what the offending spid was then to kill off all spids using tempdb (dbid=2):

select lct_admin("abort",0,2)

1> select lct_admin("abort",0,2)
2> go
lct_admin(abort): Process 25 waiting on log-suspend state on database 'tempdb is being aborted.
lct_admin(abort): Process 16 waiting on log-suspend state on database 'tempdb is being aborted.


Option 3 Increasing tempdb size


If the process is important and needs to finish then increasing the size of the tempdb database is the way to go. It might be that tempdb is undersized for the application.
use master
go
1> disk init name="tempdb_tmp12", size="400M",
2> physname="/home/sybase/SYB157/data/tempdb_tmp12.dat" , dsync="false", directio="false"

1> alter database tempdb on tempdb_tmp12=400
2> go
Extending database by 102400 pages (400.0 megabytes) on disk tempdb_tmp12


Choices of options


Hastily increasing tempdb might mean that all of the corresponding test, development and disaster recovery environments need to be changed to match, possibly due to one stray user query which shouldn't have been there in the first place.

It is for this reason that sites are sometimes reluctant to increase database sizes without good planning.



Checking progress of a rollback

The following query doesn't always give meaningful values ( it can show negative values when tempdb full) but does give an indication of progress:

select (lct_admin("logsegment_freepages",2) - 1.0 * lct_admin("reserved_for_rollbacks",2)) /1048576.*@@maxpagesize

For the above query ",2" signifies tempdb dbid = 2

This query shows 6.32 Mb free on database 16 which is in log suspend:
1> select (lct_admin("logsegment_freepages",16) - 1.0 * lct_admin("reserved_for_rollbacks",16)) /1048576.*@@maxpagesize
2> go

 ------------------------------------
                          6.320312320

(1 row affected)
1>


Prevention: Using abort tran on log full


Depending on the site and the application setting tempdb to "abort tran on log full" prevents getting into a position where tempdb is unavailable to all users.
1> use master
2> go
1> sp_dboption tempdb, "abort", true
2> go
Warning: Attempting to change database options for a temporary database. Database options must be kept consistent across all temporary databases.
Database option 'abort tran on log full' turned ON for database 'tempdb'.
Running CHECKPOINT on database 'tempdb' for option 'abort tran on log full' to take effect.
(return status = 0)
1> use tempdb
2> go
1> checkpoint
2> go


abort tran on log full tested ok for Sybase ASE 15.7 with a mixed data and log tempdb.

Creating another tempdb and binding the users to it

If users and administrators use different a tempdb from each other, then system stored procedures can still be used by "sa" logins if a user or batch process fills up tempdb.

1> create temporary database tempdb_raw on RAW_DATA002=350
2> go

1> sp_tempdb 'bind','lg','fsmith','db','tempdb_raw'
2> go
(return status = 0)



Push Symbol. Push Symbol. Door logos



Disclaimer

This page is intended to show a worked illustration of tempdb log full.

No liability can be accepted for any loss due to any information found on this page.

Alastair Cook email:
cookalastair @ btinternet.com

Page created - 12th Nov 2016


Push Pull Door