The syslogshold 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 not be consistent. 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 up 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 owning 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.