More and more tempdb problems

Recently I have played with tempdb. I have practiced a lot of scenarios with different tempdb configuration. This caused of a big mess in the configuration as I needed to perform some activities that sometimes failed and required quick repair.

So finally I had a couple of devices used by tempdb (sometimes even non-existing) and I wanted to make my Sybase server be running again.

The first error was:

Device 'tempdb2data' (with physical name '/opt/sap/databases/tempdev2data.dat', and virtual device number 13) has not been correctly activated at startup time.  Please contact a user with System Administrator (SA) role.
server  Error: 1619, Severity: 21, State: 1
server  Could not open TEMPDB, unable to continue.


The reason of this error was, that I had some files on a no more mounted (and available to be mounted) filesystem. However Sybase still contained configuration telling it, that file '/opt/sap/databases/tempdev2data.dat' should be used. The problem was quite big, because when the TempDb is not accessible many procedures are not working either! Forget about using GUI Interactive SQL. What was still working was the command line console.

To solve that problem a trick can be used. Just create the files that Sybase would like to see. This can be done with that easy step:

touch /opt/sap/databases/tempdev2data.dat
chown sap:sap /opt/sap/databases/tempdev2data.dat

when Sybase starts it will see empty file, and will start using it. Of course you need to have enough disk space on the filesystem and Sybase account needs to have full access to the file (that's why I used chown pointing to sap user and sap group - those values can be different on other systems).

The second problem was:

I wanted to remove no more useful tempdb devices. There is no easy command to do it. What needs to be done is to directly modify system tables. Following steps helped me (don't execute! Read the comment below first!):

sp_configure 'allow updates',1
GO
reconfigure with override
GO
select db_name(2)
GO
select * from sysdevices
GO
select * from sysusages where dbid = 2
GO
delete sysusages
where dbid = 2
  and vdevno = 14 --change to the value you want to use!!!
GO
shutdown
GO
sp_configure 'allow updates',0
GO

What does it mean?

• as we don't have a dedicated command to help us, we need to do all the job manually modifying system tables. This is extremely dangerous. Be careful, have backup of your databases including master and good documentation about databases, devices, segments and usages tables! By default modifying system tables is not allowed, so you need to change configuration to allow it. The last step returns to the original configuration
• we test whether the tempdb has indeed the id equal 2. This is the rule, so this step is just to explain what we are going to do in the next steps
• we display all the devices. You surely know which devices should be removed. You can validate here the names, the ids (vdevno) of devices and system paths
• we need to remove the entries from sysusages table, so let's display entries for database id=2 (tempdb)
• and very important - remove them starting from the recently added value. In my case recently added device was device with vdevno=14. You need to find the correct value by yourself. Take the highest number. If more segments should be available on one device start with the last segment (add appropriate lstart in the were clause)
• now we are in danger - the determined entry is being deleted
• at the end shutdown and restart the server. Take a look at error log. Nothing special should be there. The new tempdb should not be aware of a last segment anymore. When all the segments have been removed, you can remove the device using the sp_dropdevice command. Until the server was not restarted, in the error log following entry could be found:

kernel  udstartio: vdn 15 has not been set up

server  Error: 822, Severity: 20, State: 3

Could not start I/O for request BLKIO flags = 0x20012, size = 4096, errcode = 0x0, BUF=0x0x000000019b0c7488, MASS=0x0x000000019b0c7488, Buf#=0, page=0x0x000000019a48e000, dbid=2, Mass vdevno=14, vpage=58368, Buf lpage=80896, Mass stat=0x6811110, Buf stat=0x1, size=4096, cid=1 ('tempdb_cache'), Pinned xdes=0x(nil), spid=0.

Usually this means, that you have some hardware problem. Here the reason is known...

Another sources of information regarding this topic:
https://www.petersap.nl/SybaseWiki/index.php?title=Tempdb_space_management
https://www.slideshare.net/SAPTechnology/ase-tempdb-performance-tuning
https://sort.veritas.com/public/documents/sf/5.0MP3/solaris/html/sf_syb_admin/ch08s04s01.htm
https://groups.google.com/forum/#!topic/comp.databases.sybase/9ozI7wTXEB8