Sybase Agent Collection Failure Due to Missing Temporary Tables or Stored Procedures (4379536)

Foglight agent logs show errors like: FrameworkQueryException: tempdb..fgl_sy2_deltatable not found. or Stored procedure 'sp_fgl_deltaint' not found. Collections such as EngineSummary, ProcedureCache, NetworkIOSummary, and alarms (e.g., Connection_Usage) fail. 

• Transient objects: tempdb is recreated after an ASE restart; all temp tables in tempdb are lost.
• Ownership/permissions: If sp_fgl_* procedures were created or persist with sa/dbo ownership, or the monitoring account lacks required rights, the agent cannot recreate the tempdb tables or run the procs correctly.
• Agent restart behavior: Restarting the Foglight agent runs the install/repair scripts (e.g., rapsAdmin.sql / rapsinstall*.sql) which recreate stored procedures and temp tables if the DB rights and ownership are correct. Restart does not change DB permissions.

Step 1: Deactivate the Sybase Agent

1. Navigate to the affected Sybase agent in the Foglight UI.

2. Click Deactivate to stop the agent cleanly.

Step 2: Cleanup Residual Database Objects

1). Log in to the monitored Sybase instance and run the following SQL scripts:

 

-- Remove temporary tables (use TEMPDB)
use tempdb
go
select name, user_name(uid) as owner from sysobjects where name like 'fgl_%' and type = 'U'
go

if exists (select 1 from sysobjects where name = 'fgl_sy2_deltatable' and type = 'U')
  drop table fgl_sy2_deltatable
go
if exists (select 1 from sysobjects where name = 'fgl_processes' and type = 'U')
  drop table fgl_processes
go
if exists (select 1 from sysobjects where name = 'fgl_missed_SQLText' and type = 'U')
  drop table fgl_missed_SQLText
go
if exists (select 1 from sysobjects where name = 'tmpscc' and type = 'U')
    drop table tmpscc
go

use sybsystemprocs
go
select name, user_name(uid) as owner from sysobjects where name like 'sp_fgl_%' and type = 'P' order by name
go
-- To generate drop statements (review before running)
select 'drop procedure ' + name from sysobjects where name like 'sp_fgl_%' and type = 'P'
go
-- If you need the procedure body before dropping:
sp_helptext sp_fgl_enginesummary1
go

 

2). Run the validated drop procedure <name> statements as a user with appropriate privilieges. If the procedures are owner by sa, they must be dropped by a privilged account.

Step 3: Verify Agent User Permissions

Ensure that the monitoring user (used by the Sybase agent) has mon_role. This avoids improper persistence or ownership of agent objects.

-- Check login/roles (example)
sp_helplogins <monitoring_login>
go
-- To grant (run as DBA/sysadmin)
use master
go
grant role mon_role to <monitoring_login>
go

use tempdb
go
grant create table to <monitoring_login>
go
-- revoke role sa_role from <monitoring_login>    -- run only if appropriate
-- go

Step 4: Reactivate the Agent

1. Go back to the Foglight UI.

2. Click Activate to restart the agent.

3. Monitor the agent log for confirmation that stored procedures and temporary tables are recreated:

Installing new stored procedures

Collection EngineSummary started successfully

Step 5: Validate Collection and Alerts

1. Navigate to the agent.

2. Confirm metrics.

3. If necessary, lower thresholds temporarily to test alert triggering.

Prevention

• Always use a monitoring user with mon_role

• Avoid using sa_role for agent users

• Ensure objects are cleaned up and recreated after Sybase restarts

Additional Information

• If the monitored instance was restarted, temporary objects may be lost. Reactivation of the agent is required.

• If stored procedures persist, it may indicate they were created with sa privileges.