Sybase Tempdb 100% Full

tempdb is 100% full  ?

Ideally tempdb capacity should be sized enough to handle largest and parallel transactions. However this can’t be achieved all the time. So one of the unlikely situation occurs when tempdb becomes 100% full.

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)

 

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)