Sybase IQ IQ_SYSTEM_MAIN Out of Space



Share this article :

IQ_SYSTEM_MAIN is a special dbspace that contains structures necessary for the database to open: the IQ checkpoint log, IQ rollforward/rollback data for each committed transaction and each active checkpointed transaction, the incremental backup metadata, and database space and identity metadata. (Reference)

If you are getting error messages on “out of space” on IQ_SYSTEM_MAIN – do follow below steps,

• First of all, please avoid creating or placing user tables in the IQ_SYSTEM_MAIN. You are advisable to create another DBspace for user tables. Once you have created another user DBspace, do revoke creation on IQ_SYSTEM_MAIN.

##EXAMPLE##
CREATE DBSPACE NEWDBSPACE USING
FILE NEWDBSPACE_001 'E:\data\NEWDBSPACE_001.IQ' SIZE 10000,
FILE NEWDBSPACE_002 'E:\data\NEWDBSPACE_002.IQ' SIZE 10000,
FILE NEWDBSPACE_003 'E:\data\NEWDBSPACE_003.IQ' SIZE 10000

GRANT CREATE ON NEWDBSPACE TO NEWUSER;
REVOKE CREATE ON IQ_SYSTEM_MAIN FROM NEWUSER;
SET OPTION NEWUSER.DEFAULT_DBSPACE='NEWDBSPACE';

• Once you have created above USER DBspace. You can move the user tables from the IQ_SYSTEM_MAIN to the newly created USER DBspace. Refer here.

• Increase the IQ_SYSTEM_MAIN. Refer here.

Related posts

How to Drop Connection in Sybase IQ
How to Add New Data File into Existing DBspace (Sybase IQ)
How to Get Table Size in Sybase IQ
How to Increase/Extend Data File(s) Size in Sybase IQ
© 2018 ITsiti. All Rights Reserved
Powered by KEEM