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.

Sybase ASE database option "abort tran on log full" example

There is database option named "abort tran on log full". When the option is set to true, when the transaction log segment fills up, the transaction that cannot write to transaction log will be aborted. However when this option is not set, the transaction will be suspended and will wait until the space in the log will be available again.

This setting can be displayed using following command:

sp_helpdb 'Beta'

The result may be similar to:

to change it issue:

master..sp_dboption 'Beta', "abort tran on log full", false

Now the options should change to:

The error message recorded in the errorlog in case of filling up the log would look like this:

00:0006:00000:00033:2017/03/26 09:51:53.20 server  Space available in segment 'logsegment' has fallen critically low in database 'Beta'. All future modifications to this database will be aborted until the transaction log is successfully dumped and space becomes available.

That is difference to previous messages recorded in response to the same event in case, when the "abort tran on log full" would be set to false:

00:0006:00000:00007:2017/03/19 12:49:44.92 server  1 task(s) are sleeping waiting for space to become available in the log segment for database Beta.

Databases metadata

Quite often when the database already exists some time on the server administrator needs to read somehow information regarding that database. The most important and the most basic information can be found in sysdatabases table:

SELECT * FROM sysdatabases

There is a lot of columns here, the most important are described below, the others save some more complicated stuff that is presented in quite descriptive way by for example sp_helpdb stored procedure.

Database options

Do you remember that some options of a server can be changed using sp_configure command? If the option affects database, it should be applied using a similar sp_dboption. Logic of the function is quite alike the server sp_configure. It's general syntax is:

sp_dboption 'database_name','option name', <'true'|'false'>

That procedure must be executed from the master database.

Some of database options are:

• abort tran on log full - this option causes a transaction to be rolled back, if there is a risk of filling up the transaction log. When managing database logs dba may define actions that should be taken by Sybase engine on some levels of filling up transaction log. If the current transaction is breaching the last-chance treshold it will be rolled back when this option is used.
• allow nulls by default - when you create a new table and nothing was mentioned about null acceptance for the NULL value, than by default NULLs are not accepted (so database in background adds NOT NULL expression). When this option is enabled, than the behavior of database engine changes and columns in the table will by default accept nulls

Database: How database dies: DROP DATABASE

Every dba troubles about his databases and cares about their health. But finally each database needs to die. To remove a database ALFA use  following statement:

USE master
GO

DROP DATABASE ALFA
GO

For DROP DATABASE to succeed following conditions need to be met:

• User executing the statement must be a SA (system admin) or DBO (database owner)
• No one can use the database when it is dropped. If still some sessions exist, they can be terminated using KILL command
• Database cannot be refereced by other databases
• DROP DATABASE must be started from master database

Database: expanding and shrinking database

Even if administrator decided to create the database assigning it a lot of spare disk space, it comes to a moment, when a database needs to be expanded. Assuming database has currently size of 55 MB, additional 5 MB of disk space can be assigned to it using following command:

USE master 
GO 

ALTER DATABASE ALFA 
ON AlfaDataDev='5M'
GO

The result, could be like:

Execution time: 0 seconds
Extending database by 1280 pages (5.0 megabytes) on disk AlfaDataDev
Warning: The database 'ALFA' is using an unsafe virtual device 'AlfaDataDev'. The recovery of this database can not be guaranteed.
Execution time: 0.031 seconds

Database owner and permissions of the owner

Everything in database world must to have an owner. This is specially true regarding database itself. The owner of database has all available permissions in the database. Granting new permissions for database owner in the database does not make sense.

Changing the owner on database results in permissions to be changed, so usually if dba decides to change the ownership from LoginA to LoginB and LoginA should still have some a
ccess to the database, those permissions would need to be granted separately.

Let's suppose we have a database ALFA. We also have a server login AlfaApp. To create the login following command could be executed:

CREATE LOGIN AlfaApp WITH PASSWORD 's3cr3t5!'

To verify permissions of that user on the database try to start on behalf of the user USE statement:

[sap@localhost ~]$ isql -S192.168.2.11:5000 -UAlfaApp -Ps3cr3t5!
1> USE ALFA
2> GO
Msg 10351, Level 14, State 1:
Server 'SAP01', Line 1:
Server user id 6 is not a valid user in database 'ALFA'
1>

The error message is clear - user AlfaApp cannot access ALFA database. Who is the owner of database? This information can be displayed using sp_helpdb:

Database creation - the 3 worst practicies

Isn't it stupid, that default settings are usually not the best!? Couldn't every software be designed so, that default options are optimal and
recommended values? No. Default settings should allow the currently running process to finish successfully...

So let's take look at default options when a new database is created in Sybase. Are defaults optimal?

The shortest form

CREATE DATABASE Alfa

What will happen here? Where will the database be created? Answer is easy - on default device! If the administrator followed one of the best practices during devices administration, that the default device is on a dedicated file or disk. But if the administrator was too lazy, than the default device is still master!

Database: creating a new database

Before creating a database display the list of devices available. Take a look at device names, sizes and options. When database is being created nothing should happen incidentally.

To create a database use command like this one:

CREATE DATABASE ALFA
ON AlfaDataDev = '50M'
LOG ON AlfaLogDev

This command will create a database named ALFA and it will reside on two devices: AlfaDataDev and AlfaLogDev. Not all device space needs to be consumed by the database. As it is shown from the first device only 50 MB was taken.  In the case of the second device all space will be used by the database.
Why have we used two devices? What the syntax "ON" and "LOG ON" mean?

System databases

System databases contain basic configuration information or some default values allowing Sybase to work.

master

The most important database is master. When Sybase starts option -d points to location of master device. The entire server configuration is next loaded from master database. Having this in mind - yes, master is the most important database. Next points are just consequences of this remark:

• administrator should always have up to date backup of master database
• no user databases should be put on the master device

Database: What is a database in SAP Sybase? System tables

A database is the basic component, where user data is stored, but apart from user data, databases store also some other elements:

• indexes that speedup data retrieval
• views, functions and procedures that simplify working with data
• transaction log that is required to guarantee transactions to be consistent even if database server would be unexpectedly stopped
• system tables describing objects that bildup the database

Let's take closer look to system tables. Their names usually begin with 'sys' prefix and generally should never be modified directly (however after changing the 'allow updates' parameter it is possible):