Tempdb space management

A default installation of Sybase ASE has a small tempdb located on the master device. Almost all ASE implementations need a much larger temporary database to handle sorts and worktables and therefore DBA's need to increase tempdb. This document gives some recommendations how this can be done and describes various techniques to guarantee maximum availability of tempdb.

About Segments

Tempdb is basically just another database within the server and has three segments (What's a segment): 'system' for system tables like sysobjects and syscolumns, 'default' to store objects such as tables and 'logsegment' for the transaction log (syslogs table). This type of segmentation, no matter the size of the database, has an undefined space for the transaction log; the only limitation is the available size within the database. The following script illustrates that this can lead to nasty problems.

create table #a(a char(100) not null)
go
declare @a int

select @a = 1

while @a > 0
begin
   insert into #a values("get full")
end
go  

Running the script populates table #a and the transaction log at the same time, until tempdb is full. Then the log gets automatically truncated by ASE, allowing for more rows to be inserted in the table until tempdb is full again. This cycle repeats itself a number of times until tempdb is filled up to the point that even the transaction log cannot be truncated anymore. At that point the ASE errorlog will show messages like 1 task(s) are sleeping waiting for space to become available in the log segment for database tempdb. When you log on to ASE to resolve this problem and you run an sp_who, you will get 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.

Your first task is to kill off the process that causes the problem, but how can you know which process to kill if you even can't run an sp_who? This problem can be solved with the lct_admin function. In the format lct_admin("abort",0,<dbid>) you can kill sessions that are waiting on a log suspend. So you do a:

select lct_admin("abort",0,2) -- 2 is dbid for tempdb.

When you execute the lct_admin function the session is killed but tempdb is still full. In fact it's so full that the table #a cannot be dropped because this action must also be logged in the transaction log of tempdb. Besides a reboot of the server you would have no other option than to increase tempdb (alter database)with just a bit more space for the logsegment.

alter database tempdb log on <some_device> = <number of Mb's>

This extends tempdb and makes it possible to drop table #a and to truncate the transaction log. In a real-life situation this scenario could cause significant problems for users.

Prevention of a full logsegment

One of the database options that can be set with the sp_dboption stored procedure can be used to prevent this. When you do:

sp_dboption tempdb,"abort tran on log full",true

(for pre 12.5.1: followed by a checkpoint in tempdb) the transaction that fills up the transaction log in tempdb is automatically aborted by the server.

Default or system segments are full

The default or system segments in tempdb, where the actual data is stored, can also get full, just like any ordinary database. Your query is cancelled with a Msg 1105: Can't allocate space for object '#a_____00000180017895422' in database 'tempdb' because 'default' segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase size of the segment. This message can be caused by a query that creates a large table in tempdb, or an internal worktable created by ASE used for sorts, etc. Potentially, this problem is much worse than a full transaction log since the transaction is cancelled. A full log segment leads to "sleeping" processes until the problem is resolved. However, a full data segment leads to aborted transactions.

Prevention of a full segment for data

The Resource Governor in ASE allows you to deal with these circumstances. You can specify just how much space a session is allowed to consume within tempdb. When the space usage exceeds the specified limit the session is given a warning or is killed. Before using this feature you must configure ASE (with sp_configure)to use the Resource Governor:

sp_configure "allow resource limits",1

After a reboot of the server (12.5.1. too) you can use limits: (sp_add_resource_limit)

sp_add_resource_limit "petersap",null,"at all times","tempdb_space",200

This limit means that the user petersap is allowed to use 200 pages within tempdb. When the limit is exceeded the session receives an error message (Msg 11056) and the query is aborted. Different options for sp_add_resource_limit make it possible to kill the session when the limit is exceeded. Just how much pages a user should be allowed to use in tempdb depends on your environment. Things like the size of tempdb, the number of concurrent users and the type of queries should be taken into account when setting the resource limit. When a resource limit for tempdb is crossed it is logged into the Sybase errorlog. This makes it possible to trace how often a limit is exceeded and by who. With this information the resource limit can be tuned. When you use multiple temporary databases the limit is enforced on all of these.

Separation of data and log segments

For performance reasons it makes sense to separate the system+default and the logsegment from each other. Not all sites follow this policy. It's a tradeoff between flexibility to have data and log combined and some increased performance. Since tempdb is a heavily used database its not a bad idea to invest some time into an investigation of the space requirements. The following example illustrates how tempdb could be configured with separate devices for the logsegment and the data. The example is based on an initial setting of tempdb on the master device. First we increase tempdb for the system and data segments:

alter database tempdb on <your data device> = <number of Mb>

Then we extend tempdb for the transaction log:

alter database tempdb log on <your log device> = <number of Mb>

When you have done this and run an "sp_helpdb tempdb" you will see that data and log are still on the same segment. Submit the following to resolve this: (sp_logdevice)

sp_logdevice tempdb, <your log device>

Please note that tempdb should not be increased on the master device.

Using the dsync option

The dsync option for devices allows you to enable/disable I/O buffering to file systems. The option is not available for raw partitions and NT files. To get the maximum possible performance for tempdb use dedicated device files, created with the Sybase disk init command. The files should be placed on file system, not on raw partitions. Set the dsync option off as in the following example: (disk init)

disk init name = "tempdb_data",
   size= "500M",
   physname= "/var/sybase/tempdb_data.dat",
   dsync = false  

Moving tempdb off the master device

When you have increased tempdb on separate devices you can configure tempdb so that the master device is unused. This increases the performance of tempdb even further. There are various techniques for this, all with their pros and cons but I recommend the following. Modify sysusages so that segmap will be set to 0 for the master device. In other words, change the segments of tempdb so that the master device is unused. This can be done with the following statements:

sp_configure "allow updates to system tables",1
go
update master..sysusages
   set segmap = 0
   where dbid = 2
   and lstart = 0
go
sp_configure "allow updates to system tables",0
go
shutdown -- reboot now!
go  

When you use this configuration you should know the recovery procedure just in case one of the devices of tempdb gets corrupted or lost. Start your ASE in single user mode by adding the -m switch to the dataserver options. Then submit the following statements:

update master..sysusages
    set segmap = 7
    where dbid = 2
    and lstart = 0
go
delete master..sysusages
   where dbid = 2
   and lstart > 0
go
shutdown -- reboot now!
go 

Remove the -m switch from the dataserver options and restart ASE. Your tempdb is now available with the default allocation on the master device.

Summary of the recommendations

• Increase tempdb from it's initial size to a workable value
• Set the option "abort tran on log full" for tempdb to on
• Create resource limits
• Place data and log segments on separate devices
• Place tempdb on filesystem with dsync set to false
• Move tempdb off the master device by modifying the segmap attribute