The syslogshold table is located in the master database.
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 SAP ASE 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.
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.