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.
The initial log records for a transaction may reside
in a user log cache, which is not visible in syslogshold until
the records are flushed to the log (for example, after a checkpoint).
For more information about the syslogshold system table, see the Reference Manual. For information about the last-chance threshold and threshold procedures, see Chapter 31, “Managing Free Space with Thresholds.”
