Providing enough log space

Every use of dump transaction...with no_log is considered an error and is recorded in the server’s error log. If you have created your databases with log segments on a separate device from data segments, written a last-chance threshold procedure that dumps your transaction log often enough, and allocated enough space to your log and database, you should not have to use this option.

However, some situations can still cause the transaction log to become too full, even with frequent log dumps. The dump transaction command truncates the log by removing all pages from the beginning of the log, up to the page preceding the page that contains an uncommitted transaction record (known as the oldest active transaction). The longer this active transaction remains uncommitted, the less space is available in the transaction log, since dump transaction cannot truncate additional pages.

This can happen when applications with very long transactions modify tables in a database with a small transaction log, which indicates you should increase the size of the log. It also occurs when transactions remain uncommitted for long periods of time, such as when an implicit begin transaction uses the chained transaction mode, or when a user forgets to complete the transaction. You can determine the oldest active transaction in each database by querying the syslogshold system table.

The syslogshold table is in the master database. Each row in the table represents either:

• The oldest active transaction in a database, or

• The Replication Server truncation point for the database’s log.

A database may have no rows in syslogshold, a row representing one of the above, or two rows representing both of the above. For information about how a Replication Sever truncation point affects the truncation of the database’s transaction log, see the Replication Server documentation.

Querying syslogshold provides a snapshot of the current situation in each database. Since most transactions last for only a short time, the query’s results may be inconsistent. For example, the oldest active transaction described in the first row of syslogshold may finish before Adaptive Server completes the query of syslogshold. However, when several queries of syslogshold over time query the same row for a database, that transaction may prevent a dump transaction from truncating any log space.

When the transaction log reaches the last-chance threshold, and dump transaction cannot free space in the log, you can query syslogshold and sysindexes to identify the transaction holding up the truncation. For example:

select H.spid, H.name
from master..syslogshold H, threshdb..sysindexes I
where H.dbid = db_id("threshdb")
and I.id = 8
and H.page = I.first

spid    name
------  -------------------------------------
     8  $user_transaction

(1 row affected)

This query uses the object ID associated with syslogs (8) in the threshdb database to match the first page of its transaction log with the first page of the oldest active transaction in syslogshold.

You can also query syslogshold and sysprocesses in the master database to identify the specific host and application owning the oldest active transactions. For example:

select P.hostname, P.hostprocess, P.program_name,
   H.name, H.starttime
from sysprocesses P, syslogshold H
where P.spid = H.spid
and H.spid != 0

hostname hostprocess program_name name               starttime
-------- ----------- ------------ ------------------ ------------------
eagle          15826 isql         $user_transaction  Sep  6 1997 4:29PM
hawk           15859 isql         $user_transaction  Sep  6 1997 5:00PM
condor         15866 isql         $user_transaction  Sep  6 1997 5:08PM

(3 rows affected)

Using the above information, you can notify or kill the user process that owns the oldest active transaction and proceed with the dump transaction. You can also include the above types of queries in the threshold procedures for the database as an automatic alert mechanism. For example, you may decide that the transaction log should never reach its last-chance threshold. If it does, your last-chance threshold procedure (sp_thresholdaction) alerts you with information about the oldest active transaction preventing the transaction dump.