sysadmin DB relocation
Dennis Melnikov asked how to move the sysadmin database out of rootdbs on IDS 11.70.FC5XE to avoid overflow during SQL tracing. Art Kagel pointed to a procedure for relocating it and advised disabling the task that copies SQLTRACE data into sysadmin. Dennis said the scheduler was already disabled, so Art noted nothing should be writing there. Dennis said there was no actual overflow problem.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, IBM Informix Dynamic Server Version 11.70.FC5XE Please, how to relocate sysadmin database from rootdbs to avoid rootdbs overflow during SQL tracing? ------------------------------ Sincerely, Dennis ------------------------------
See this: Moving (Resetting) the sysadmin database to a new dbspace in IBM Informix Dynamic Server (IDS) ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
That said, I STRONGLY recommend that you turn off the task manager task that is copying the SQLTRACE data into sysadmin and instead create an external script or program to do that copy to a location NOT inside the database being monitored. This copy operation has far more impact on your server's performance than the SQLTRACE itself!!!!! And that's not even considering that sysadmin is a logged database and so the copy is consuming logical logs very quickly which could make recovery from a crash take far longer than it should. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Art: We have the scheduler turned off on startup: SCHAPI: "INFORMIXDIR/etc/sysadmin/stop" file is present. Bypassing dbScheduler and dbWorker threads startup. ------------------------------ Sincerely, Dennis ------------------------------
Dennis: If the task manager scheduler is shut down then SQLTRACE is not writing to sysadmin at all. SQLTRACE itself only writes to the configured ring buffer in memory. Only the " Save SQL Trace" task in the scheduler writes to the sysadmin mon_sqltrace* tables. I don't know what else might be consuming space there, but with the scheduler down it really is unlikely. What tables are being populated/growing? Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Art: Thank you so much. There is no problem of rootdbs overfill, I just wanted to prevent it. ------------------------------ Sincerely, Dennis ------------------------------