Database startup and shutdown

Note: The first part of this section describes the shutdown and startup options up to Firebird 2.0. There is a separate section at the end which discusses the new states for starting and stopping a database using Firebird 2.0 onwards.

Database shutdown

If there is maintenance work required on a database, you may wish to close down that database under certain circumstances. This is different from stopping the Firebird server as the server may well be running other databases which you do not wish to affect.

The command to close a database is:

 gfix -shut OPTION TIMEOUT database_name

The TIMEOUT parameter is the time, in seconds, that the shutdown must complete in. If the command cannot complete in the specified time, the shutdown is aborted. There are various reasons why the shutdown may not complete in the given time and these vary with the mode of the shutdown and are described below.

The OPTION parameter is one of the following:

-at[tach] - prevents new connections.
-tr[an] - prevents new transactions.
-f[orce] - simply aborts all connections and transactions.

When a database is closed, the SYSDBA or the database owner can still connect to perform maintenance operations or even query and update the database tables.

Note: If you specify a long time for the shutdown command to complete in, you can abort the shutdown by using the -online command (see below) if the timeout period has not completed.

Preventing new connections

-at[tach]: this parameter prevents any new connections to the database from being made with the exception of the SYSDBA and the database owner. The shutdown will fail if there are any sessions connected after the timeout period has expired. It makes no difference if those connected sessions belong to the SYSDBA, the database owner or any other user. Any connections remaining will terminate the shutdown with the following details:

 linux> gfix -shut -attach 5 my_employee
 lock conflick on no wait transaction
 -database shutdown unsuccessful

Anyone other than the SYSDBA or database owner, attempting to connect to the database will see the following:

 linux> isql my_employee -user norman -password whatever
 Statement failed, SQLCODE = -901
 database my_employee shutdown
 Use CONNECT or CREATE DATABASE to specify a database
 SQL>

Connections in the database will still be able to start new transactions or complete old ones.

Preventing new transactions

-tr[an]: prevents any new transactions from being started and also prevents new connections to the database. If there are any active transactions after the timeout period has expired, then the shutdown will fail as follows:

 linux> gfix -shut -tran 5 my_employee
 lock conflick on no wait transaction
 -database shutdown unsuccessful

If any user connected to the database being shutdown with the -tr[an] tries to start a new transaction during the shutdown timeout period, the following will result:

 SQL> select * from test;
 Statement failed, SQLCODE = -902
 database /home/norman/firebird/my_employee.fdb shutdown in progress
 Statement failed, SQLCODE = -902
 database /home/norman/firebird/my_employee.fdb shutdown in progress
 Statement failed, SQLCODE = -901
 Dynamic SQL Error
 -SQL error code = -901
 -invalid transaction handle (expecting explicit transaction start)

Force closure

-f[orce]: shuts down with no regard for the connection or transaction status of the database. No new connections or transactions are permitted and any active sessions are terminated along with any active transactions.

Anyone other than SYSDBA or the database owner trying to connect to the database during the timeout period will not be able to connect successfully or start any (new) transactions.

Be nice to your users, use the -f[orce] option with great care.

Warning: There is a bug in Classic Server which still exists at version 2.0. The bug is such that the -f[orce] option behaves in exactly the same way as the -at[tach] option.

Starting a database

Once all maintenance work required on a database has been carried out, you need to restart the database to allow normal use again. (See Shutdown option above for details of closing a database.)

The -o[nline] command allows a database to be restarted. It takes a single parameter which is the database name as follows:

 gfix -o[nline] database_name

The following example shows a closed database being started.

 linux> gfix -online my_employee

New startup and shutdown states in Firebird 2.0

The above discussion of stopping and starting a database apply to all versions of the server up to version 2.0. From 2.0 the commands will work as described above, but a new state has been added to define exactly how the database is to be stopped or started. The commands change from those described above to the following:

 gfix -shut STATE OPTION TIMEOUT database_name

 gfix -o[nline] STATE database_name

STATE is new in Firebird 2.0 and is one of the following:

normal - This is the default state for starting the database backup. It allows connections from any authorised users - not just SYSDBA or the database owner. This option is not accepted for shutdown operations.
multi - This is the default mode as described above. When the database is shutdown as above, or using the multi state, then unlimited connections can be made by the SYSDBA or the database owner. No other connections are allowed.
single - Similar to the multi option above, but only one SYSDBA or database owner connection is allowed.
full - shutdown and don't allow any connections from anyone, even SYSDBA or the database owner. This is not an acceptable option for starting up a database.

Note: There is no leading dash for the state parameters, unlike the command itself and the -shut OPTION.

There is a hierarchy of states for a database. The above list shows them in order with normal at the top and full at the bottom.

This hierarchy is important, you cannot shutdown a database to a higher or equal level that it currently is, nor can you startup a database to a lower or equal level.

If you need to identify which level a database is currently running at, gstat will supply the answers. The following example puts a database fully online then progressively shuts it down to fully offline. At each stage, gstat is run to extract the Attributes of the database.

 linux> gfix -online normal my_employee
 linux> gstat -header my_employee | grep Attributes

         Attributes

 linux> gfix -shut multi -attach 0 my_employee
 linux> gstat -header my_employee | grep Attributes

         Attributes              multi-user maintenance

 linux> gfix -shut single -attach 0 my_employee
 linux> gstat -header my_employee | grep Attributes

         Attributes              single-user maintenance

 linux> gfix -shut full -attach 0 my_employee
 linux> gstat -header my_employee | grep Attributes

         Attributes              full shutdown

 linux>

back to top of page
<< Database housekeeping and garbage collection | Firebird Database Housekeeping Utility | Database page space utilisation >>